INNER and LEFT OUTER join help

Question

Say I have 3 tables. TableA, TableB, TableC

I need to operate the recordset that is available after a INNER JOIN.

Set 1 -> TableA INNER JOIN TableB
Set 2 -> TableC INNER JOIN TableB

I need the Set 1 irrespective of if Set 2 is empty or not (LEFT OUTER JOIN) comes to mind.

So essentially, I am trying to write a query and have come this far

SELECT *
  FROM TableA 
       INNER JOIN TableB ON ...
       LEFT OUTER JOIN (TableC INNER JOIN TableB)

How would I write in SQL Server?

EDIT: In reality, what I am trying to do is to join multiple tables. How would your response change if I need to join multiple tables ex: OUTER JOIN OF (INNER JOIN of TableA and TableB) and (INNER JOIN OF TableC and TableD) NOTE: There is a new TableD in the equation

Answer 1

Select * from ((((TableA a inner join TableB b on a.id = b.id) 
                left outer join TableC c on b.id = c.id)
                full outer join TableD d on c.id = d.id)
                right outer join TableE e on e.id = d.id)
                /* etc, etc... */

You can lose the brackets if you want.

Answer 2

 SELECT * FROM TableA 
       INNER JOIN TableB ON TableB.id = TableA.id
       LEFT JOIN TABLEC ON TABLEC.id = TABLEB.id

I Don't know what columns you are trying to use but it is just that easy

Edit: Looking at your edit it seems that you are confused about what Joins actually do. In the example I have written above you will recieve the following results.

Columns -> You will get all of the columns for TableA,TableB and TableC

Rows-> You will start off with all of the rows from tableA. Next you will remove all rows from TableA that do not have a matching "id" in Table B.(You will have duplicates if it is not a 1:1 relationship between TableA and TableB).

Now if you take the results from above you will match any records from TableC that match the TableB.id column. Any rows from above that do not have a matching TableC record will get a null value for all of the columns from TableC in the results.

ADVICE- I am betting that only part of this made sense to you but my advice is that you start writing some queries, predict the results and then see if your predictions are correct to see if you understand what it is doing.

Answer 3

try this..

SELECT *
  FROM TableA              a 
       INNER JOIN TableB   b ON a.id=b.id
       LEFT OUTER JOIN (SELECT *
                            FROM TableC            c
                                INNER JOIN TableD  d on c.id=d.id
                       ) dt on b.id=dt.id

Answer 4

Assuming I Understand your question, I think this is what you're asking for:

SELECT * 
FROM TableA INNER JOIN TableB on TableA.JoinColumn = TableB.JoinColumn
LEFT OUTER JOIN TableC on TableB.JoinColum = TableC.JoinColumn
INNER JOIN TableD on TableC.JoinColumn = TableD.JoinColumn

Note that the JoinColumn used to join A & B doesn't necesarilly have to be the same column as the one used to join B & C, and so on for C & D.

Answer 5

What you want isn't a JOIN but a UNION.

SELECT * FROM TableA INNER JOIN TableB ON ...
UNION
SELECT * FROM TableC INNER JOIN TableD ON ...

Answer 6

You didn't give your join conditions or explain how the tables are intended to be related, so it's not obvious how this might be simplified.

SELECT a.a_id, b1.b_id b1_id, b2_id, bc.c_id
FROM TableA a JOIN TableB b1 on a.b_id = b1.b_id
LEFT JOIN (SELECT c.c_id, b2.b_id b2_id
    FROM TableC c JOIN TableB b2 ON c.b_id = b2.b_id
  ) bc ON bc.c_id = a.c_id;

Looking at your latest edit, you can do something along the lines of:

SELECT <columns>
FROM (SELECT <columns> FROM TableA JOIN TableB ON <A-B join conditions>)
           LEFT JOIN
           (SELECT <columns> FROM TableC JOIN TableD ON <C-D join conditions>)
           ON <AB-CD join conditions>

Although you don't actually need the inner projections, and can do:

SELECT <columns>
FROM (TableA a JOIN TableB b ON <A-B join conditions>)
           LEFT JOIN
           (TableC c JOIN TableD d ON <C-D join conditions>)
           ON <AB-CD join conditions>

Where the AB-CD join conditions are written in terms of columns of a, b, c, d etc directly.

Answer 7

You can actually add an ordering to your joins just like in a math equation where you might do this: (5 + 4) * (3 + 1).

Given the second part of your question, give this a try:

SELECT
     <your columns>
FROM
     (TableA INNER JOIN Table B ON <join criteria for A to B>)
LEFT OUTER JOIN
     (TableC INNER JOIN Table D ON <join criteria for C to D>) ON
     <join criteria for AxB to CxD>

Answer 8

Since you're using Sql Server, why not create views that help you? Stuffing everything in a gigantic Sql statement can become hard to read. An example view might look like:

create view AandB
as
select *
from A
inner join B on B.aid = A.aid

And the same for CandD. Then you can retrieve the optional join with simple Sql:

select *
from AndB
left outer join CandD on AndB.cid = CandD.cid

If you're interested in rows from both sets, you can do a full join:

select *
from AndB
full outer join CandD on AndB.cid = CandD.cid

Answer 9

SELECT *
  FROM TableA A
 INNER JOIN TableB B  ON B.?? = A.??  AND ...
  LEFT JOIN TableC C  ON C.?? = B.??  AND ...
  LEFT JOIN TableB B2 ON B2.?? = C.?? AND ...
  LEFT JOIN TableD D  ON D.?? = C.??  AND ...

So here's the thing: logically, joins aren't actually between specific tables, they are between a table and the rest of the "set" (of joins and tables). So while you know that there is a 1-to-1 relationship between C and B2 or between C and D, you can't INNER JOIN to C because C could be null from it's LEFT JOIN to B, which will eliminate those rows, effectively undoing your LEFT join.

So basically, any joins to a table that's LEFT outer joined must also be LEFT outer joined. Does this make sense?