SQL GURU's... Complex JOIN - 'JOIN WHERE'

f9073341

Registered User.
Local time
Today, 09:19
Joined
Mar 7, 2009
Messages
27
Hi guys,

What would be the syntax of a join query when you have multiple joins, and need one of them to filter with a 'WHERE'?

E.g.

SELECT Table_A.Name, Table_B.Age, Table_C.Address, Table_D.Gender
FROM Table_A
LEFT JOIN (((Table_B ON Table_A.Name=Table_B.Name)
LEFT JOIN (Table_C ON Table_A.Name=Table_C.Name)
LEFT JOIN (Table_D ON Table_A.Name=Table_D.Name
WHERE Table_D.Name='John')

PS: That's just an example... it's the syntax I'm after.

Much appreciated!
 
You create the joins you want then add the condition to the criteria under the desired column(s)
 
Thanks for your reply DCrake. After you've declared all the JOINs? Or after each join?
 
If you want only records in all joins where there is a common filter then place the condition on the parent table. Then only records that match in all the three joined tables match, however you need to watch out for caterian effects occuring.
 
In terms of setting the criteria, I think it would depend upon which table has the most records, which of these two tables is the parent table and on what field is the condition being placed.

Like David mentioned, if the field exists between both tables then you should filter on the parent table. But that would fully apply if it is an OUTTER JOIN (i.e. LEFT or RIGHT join) from the parent table. If it's an INNER JOIN and the parent table has the least records then filter on the parent. However, if it's an INNER JOIN and the child has the least records you filter on the child.
 

Users who are viewing this thread

Back
Top Bottom