There are people who probably look at complex joins and see them diagrammed in their head (BrentO comes to mind), but I'm not one of them. I fully understand the differences between INNER, LEFT OUTER and RIGHT OUTER JOINS, but what happens when you go beyond a couple of tables? Especially if you need a few different OUTER JOINS. Start by creating a couple of tables and populating them using this script. Within that script, you'll find a simple multi-table JOIN:

SELECT     a.ID AS aID, a.Value AS aValue, c.ID AS cID, c.Value AS cValue,
    e.ID AS eID, e.Value AS eValue, g.ID AS gID, g.Value AS gValue
FROM         dbo.table1 AS a INNER JOIN dbo.table2 AS c INNER JOIN
    dbo.table3 AS b ON c.ID = b.ID2 ON b.ID1 = a.ID INNER JOIN
    dbo.table7 AS f INNER JOIN dbo.table6 AS g ON f.ID2 = g.ID
    ON a.ID = f.ID1 LEFT OUTER JOIN dbo.table4 AS e INNER JOIN
    dbo.table5 AS d ON e.ID = d.ID2 ON c.ID = d.ID1
WHERE     (a.ID = 3)

What we want the JOIN to do is to return the data from table1 and include the data from the other tables if it exists but not to use them as a filter, which means we'll be using OUTER JOINs on f-a and a-b. And, with a query this simple, it's easy enough to write them manually, there's a trick that can make more complex queries easier to deal with. Create a view in SMS and build your JOINs graphically. If you drop this SELECT statement into the VIEW window, you'll see something like this:

This presents a graphical display of how the tables are joined together. Most of the tables are using INNER JOIN but, as you can see on the right, there's an OUTER JOIN between c and d. You can tell which table drives the JOIN by the fact that there's a half square on the c side, which means we'll get all the data from c whether or not there's any matching data in d. To ensure we get all of our data from a, we right-click on the join link and choose "Select All Rows from a". As you can see, I've already done that on the join between a and f and am in the midst of doing it between a and b.

This leaves us with a query that functions the way we want it to:

 

SELECT     a.ID AS aID, a.Value AS aValue, c.ID AS cID, c.Value AS cValue,
    e.ID AS eID, e.Value AS eValue, g.ID AS gID, g.Value AS gValue
FROM dbo.table1 AS a LEFT OUTER JOIN dbo.table2 AS c INNER JOIN
    dbo.table3 AS b ON c.ID = b.ID2 ON b.ID1 = a.ID LEFT OUTER JOIN
    dbo.table7 AS f INNER JOIN dbo.table6 AS g ON f.ID2 = g.ID
    ON a.ID = f.ID1 LEFT OUTER JOIN dbo.table4 AS e INNER JOIN
    dbo.table5 AS d ON e.ID = d.ID2 ON c.ID = d.ID1
WHERE     (a.ID = 3)

But wait, that's not all! Another great use of the view builder is that you can go into the SQL pane, add an alias for a table and click in the Diagram Pane. This will automagically update all of the column references!

That's today's helpful hint +1