SQL Server Reference Guide

One of the most difficult things to do in the Sequential Query Language (SQL) is to learn how to do proper JOIN operations. Older versions of SQL used a series of join operations in the WHERE clause that were fairly easy to understand. The problem was that these joins could not handle all of the situations that developers needed, so a newer version of the JOIN syntax was created that is more precise and exact.

The syntax really isn’t that difficult to understand, but it does take some practice. For simple table joins as I described in the last article in this series, you can pick up the syntax quickly, especially when the join involves a “parent” table that has a one-to-many relationship with “children” tables. By the way, if those terms are new to you, please check out this series of articles before you continue. If the joins, however, involve lots of tables or complex table layouts, it might not be as simple to construct.

Once you create the SELECT statement with the complex joins in it, it’s a simple matter to create a view that stores the statements for you. As I’ve described earlier, a database View is a great way to limit and format the data a user or program needs to see. By selecting only the columns you need to show, and then limiting the rows returned with a WHERE clause, you effectively control what the users work with. In the case of reporting, a database View makes the reporting much simpler by exposing a table-like structure that brings together lots of other tables.

You’ll normally start with a description of what your users want to see. They will say, “show me all of the orders with detail lines on a given date, by customer and region,” or, “find all people that are doctors that have attended at least three seminars in the last two years.” These are more complex statements that dive past some of the basic concepts we’ve studied here. But like most any complex problem, you just have to break it down to easier steps, and then put those together. That’s what I’ll show you how to do in this tutorial.

To work through these concepts, it’s useful to take an actual database and deal with it that way. I’ve set up a database that I use in my classes that I teach, so we’ll use that here. It deals with the “Washington Agricultural Veterinarian Services” (WAVS), a fictitious company I use to teach my students how to create a database design from a set of requirements, just as I’ve described here on this site.

WAVS is a company that has a few “agricultural” vets, the kind that take care of farm animals, and it includes animals and visits to the farms to take care of the animals. All that really isn’t important, other than to explain the question the vet has asked: Can I see the visit information for each animal?

Before I can answer that question, you might want to examine Entity Relationship Diagram (ERD) that I created from the database design. It has some interesting concepts built in, and even a few errors, which you might be able to spot if you look at the complete ERD. And that’s where I start to do complex joins - with the design. While you don’t have to have an ERD to do them, it does make things easier. So that’s step one: look at the design of the database.

Step One: Start with the database design

In the following diagram, I’ve opened a SQL Server Database Diagram for this database. Now, I’m not thrilled with the Database Diagram tool in SQL Server, but it is universal to the systems you’ll see and does give us a base to talk from. One of its major shortcomings is that it does not show the traditional “crow’s feet” that shows the relationship types (many to one, one to one and so on) and does not show “optimality” or whether an attribute (column) must have a value or not. But for this diagram, you can interpret that all of the “infinity” symbols (the two little circles) means “many” and the key symbol indicates that there must be a value in the “child” table:

Figure 1

The reason I’m using this database instead of the sample databases that come with SQL Server is that this one has an interesting design. You’ll notice that there are five “basic” entities (tables) that stand alone, with no relation to each other:

Person — Holds both vets and clients, and anyone else the system needs to store. The type of person is stored in “PersonType.”

Organization — This table holds any company we work with, from WAVS itself to farms and zoos, and could even hold vendors if needed. The type of organization is stored in “OrganizationType.”

Subject — This is where the animals are kept. I’ve cheated a little in this design and stored all of the details of the animal off in a document on the hard drive, and then pointed to that location in “SubjectDetails.” A more complete design would have all of the animal’s information in more columns.

Assignment - Contains the “call” to the vet’s office. Whenever a client calls in for a procedure for one of their animals, the call is stored here.

Visit - Stores the actual site-visit the vets make to an animal, on a farm, based on an assignment.

What makes the design interesting is that since each one of the base tables has a many-to-many relationship with each other, I’ve created multiple “tertiary” or “join” tables. That makes the design very flexible, since now multiple people can be related to multiple companies, multiple visits can be placed against multiple assignments and so on. But that flexibility comes at a cost: the joins to get the original question answered can become rather cumbersome. But if we take our time, it isn’t difficult at all.

Step Two: Define the Entities (tables) where the information lies

Looking at the question, I now need to list out the tables where the information lives. I create a set of comments, something I do with all but the most basic of queries:

/*
a.Person
b.Organization
c.Subject
d.Visit
e.Assignment
f.PersonOrganization
g.SubjectOrganization
h.AssignmentPerson
*/

All I’ve done here is list the tables I think might satisfy the query. I’ll remove what I don’t need later. I’ve also prefaced each table with a letter, just to make an “alias” later. It simplifies the typing, and keeps me from spelling something incorrectly in the query.

Step Three: Find the “central” table or tables

I start with the “central” part of the question — in this case, the “visit.” You don’t have to do this, and in some cases, it might not even be a single table. But I have to start somewhere, so I look and see that my visit information lies in the “Visit” table. That’s where I’ll start the query:

SELECT
 d.DateOfVisit
, d.SOAPNotesLocation
FROM
Visit d

This is pretty simple stuff — I pulled the date of the visit (d.DateOfVisit) and the “SOAP” notes that the vet uses (d.SOAPNotesLocation) from the “Visit” table. But notice that I gave the table an “alias,” or another name. You’ve seen me do this before.

I run this query to make sure it brings the data back that I expect.

Step Four: Add the next table with an INNER or OUTER join

Now I look for the next closest piece of data that I need. I see that I need the name of the animal, or at least the number that the farmer uses to identify it. That’s the “SubjectID” field in the “Subject” table. That’s a fairly simple join on two fields: the SubjectID in the Visit table and the SubjectID in the Subject table. Since I only need the visits that include a specific set of animals, I use an INNER join, meaning “get the ones that are equal.” If I needed all animals regardless of whether they had a visit or not, I would use an OUTER join, pointing to the side (LEFT OUTER or RIGHT OUTER) based on the selection order.

So that brings me here:

SELECT
 c.SubjectIdentifier
, d.DateOfVisit
, d.SOAPNotesLocation
FROM
Visit d
	INNER JOIN [Subject] c
	ON d.SubjectID = c.SubjectID

Simple enough — not a complicated join at all. Now I have the animal’s name or number.

Step Five: Add additional JOINs, even when the SELECT doesn’t ask for fields from them

This is the most interesting part of the join sequence. You can see from the diagram that I need to get to the organization that the animal belongs to, but they aren’t directly related. That means I’ll have to involve the animal table (Subject), the company table (Organization) and the table that joins them (SubjectOrganization). The SubjectOrganization table is the key — quite literally. It has keys for both the animal (SubjectID) and the companies (OrganizationID) that brings the two together. And I’ll get multiple rows (potentially) back for each, which is exactly what they are supposed to do. Here’s how that looks:

SELECT
 b.OrganizationName
, c.SubjectIdentifier
, d.DateOfVisit
, d.SOAPNotesLocation
FROM
Visit d

	INNER JOIN [Subject] c
	ON d.SubjectID = c.SubjectID
	INNER JOIN SubjectOrganization g
	ON c.SubjectID = g.SubjectID
	INNER JOIN Organization b
	ON g.OrganizationID = b.OrganizationID

You can see that I’ve “skipped” some of the columns in the SELECT, since the user really doesn’t care about those “tertiary” join tables at all. By now it’s pretty obvious what we’re doing — just building on one set of data from the previous one. That’s exactly the power of a Relational Database Management System (RDBMS) — it allows you to have a lot of parts that you just snap back together whenever you need them.

I’ll just repeat this process for the owner of the animal. They are related to the animal by several tables, such as the ones I’ve already included. Going further around the Database Diagram you can see the next table I need to include is “Person,” but of course I can’t get there directly. I have to include the “PersonOrganization” first, using the “OrganizationID” between them, and then the “Person” table, using the “PersonID” between those. So now I’ll put all that together in one query:

SELECT 
 a.PersonName
, b.OrganizationName
, c.SubjectIdentifier
, d.DateOfVisit
, d.SOAPNotesLocation
FROM
Visit d
	INNER JOIN [Subject] c
	ON d.SubjectID = c.SubjectID
	INNER JOIN SubjectOrganization g
	ON c.SubjectID = g.SubjectID
	INNER JOIN Organization b
	ON g.OrganizationID = b.OrganizationID
	INNER JOIN PersonOrganization f
	ON b.OrganizationID = f.OrganizationID
	INNER JOIN Person a
	ON f.PersonID = a.PersonID

And there you have it. Now I have all of the information I was asked for in this report.

Step Six: Add any WHERE or ORDER BY information needed

At the very end, once I have all of the information I was asked for to do the report, I add any “limiting” clauses, such as WHERE or ORDER BY.

If I want to use this information again later, I can save it as a view with a very simple addition to the very top of the query:

CREATE VIEW viewname
AS

Keep in mind that a view doesn’t want an ORDER BY clause, since it is treated as a table to the user. It can have the WHERE clause, though.

To build your own complex queries, you can follow these same simple instructions. If you don’t have the Database Diagram or ERD, you can just list out the table fields and any Keys or other relationships they have to create really intricate views.

InformIT Articles and Sample Chapters

Need a primer on Transact-SQL? Check out my series here.

Books and eBooks

There’s a lot more on database design in this book by Eric Johnson, Joshua Jones called Developer's Guide to Data Modeling for SQL Server, A: Covering SQL Server 2005 and 2008. (Read in Safari Books Online)

Online Resources

The full Transact-SQL (T-SQL) reference is here.