LEFT JOINING two INNER JOINS

Diango

Just double checking with you guys, but is this an acceptable way of joining two inner joins:

Select column1, column2, column3

FROM table 1 INNER JOIN table 2

ON

table1.column = table2.column

LEFT JOIN table 3 INNER JOIN Table 4

ON table3.column = table4.column

ON

table3.column = table1.column

Kent Waldrop Jl07

I think there needs to be an ON condition stated between the LEFT JOIN for table 3 and the INNER JOIN for table 4. Also, it appears to me that what you are wanting to do is to LEFT JOIN to a DERIVED TABLE that is the product of the join of tables 3 and 4. That might look something like:

Code Snippet
Select column1, column2, column3
  FROM table1 
  JOIN table2 
  ON table1.column = table2.column
 LEFT JOIN 
( select table3.column,
             otherAppropriateTables
       from table3
       join table4
         on table3.column = table4.column
    ) derivedTable
   on derivedTable.column = table1.column

Diango

I know about doing it as a derived table. I you could do a LEFT JOIN on two INNER JOINS.

Arnie Rowland

You have been offered, and seem to reject a reasonable solution.

And since you know about using a derived table as Kent mentioned,

and since you also clearly know that your code as posted does work exactly like you hoped,

What is the question

Diango

easy! I did not reject his solution. I know about doing it the way he did it. My question is, is it acceptable doing it the way I showed And would it work that way

Arnie Rowland

My regrets if I came off 'too strong'.

Yes, it will work as you have presented the code.

If you examine the execution plan, you will see that both INNER JOINs occur, and then there is a LEFT JOIN between the sets.

It is acceptable. However, I would clearly leave documentation (Comments) in the code since most folks will not have seen this particular permutation, and won't understand what is occurring.

There definitely is a different result than just the 'normal'

INNER JOIN ... ON

LEFT JOIN ... ON

INNER JOIN ... ON

It is clearly acceptable if it provides the resultset you desire.

Diango

Ah ok cool, Thanks! By using it the way I did it, I would definetly have to leave comments to effectively show what's happening.

Umachandar Jayachandran - MS

The derived table approach is a roundabout way of doing things. There is a much shorter and succinct syntax using joined table notation. SQL Server supports the ANSI SQL syntax for SELECT and joined tables so you can do below:

Code Snippet
create table t1 ( i int not null );
create table t2 ( i int not null );
create table t3 ( i int not null );
create table t4 ( i int not null );
 
insert into t1 (i) values (1);
insert into t2 (i) values (1);
insert into t1 (i) values (3);
insert into t2 (i) values (3);
insert into t3 (i) values (2);
insert into t4 (i) values (2);
insert into t3 (i) values (3);
insert into t4 (i) values (3);
 
select *
from t1
join t2
on t2.i = t1.i
left join (t3 join t4 on t4.i = t3.i)
on t3.i = t1.i
 
-- yet another way of writing the same:
select *
from t1
join t2
left join (t3 join t4 on t4.i = t3.i)
on t3.i = t2.i
on t2.i = t1.i
 
drop table t1, t2, t3, t4;

Look for the joined_table syntax in the FROM clause topic in BOL.

Arnie Rowland

As Umachadar indcates in his examples, using parentheses helps to provide a visual (as well as structural) indication of the 'order of precedence' for the JOINs.

LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS

Re: LEFT JOINING two INNER JOINS