Join multiple tables

[tweenerz]

I am in the process of converting an access db into a mysql db. Due to Access limitations, one table was separated into 5 different tables. I need to join them together into one table.

Can this be done through a select statement (possibly multiple left joins). the problem is that certain rows may not exist in some tables so a straight equality sql statement will not catch everything.

So if I have the following tables:

j1 (1000 records) (main table)
j2 (850 records)
j3 (900 records)
j4 (989 records)

Each table has a common key - say j_key

how can I get a result set of 1000 records containing any related records in all the other tables? If I do an equality join, I will only get back records which have an entry in every table.

[highlight]I need all the records in the j1 table along with anything that may be in the other tables with the same key.[/highlight]

I hope that this is clear enough.

 

[PHV]

Have you tried something like this ?
SELECT j1.*,j2.*,j3.*,j4.*,j5.*
FROM j1 LEFT join j2 ON j1.j_key=j2.j_key
LEFT join j3 ON j1.j_key=j3.j_key
LEFT join j4 ON j1.j_key=j4.j_key
LEFT join j5 ON j1.j_key=j5.j_key
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tweenerz]

That was my first attempt, but I keep getting a syntax error as soon as I try to do more than one left join. Once I put in 'LEFT join j3 ...' it flags an error.

Does Access support multiple left joins?

 

[tweenerz]

We were almost there:

SELECT j1.*,j2.*,j3.*,j4.*,j5.*
FROM ((j1 LEFT join j2 ON j1.j_key=j2.j_key
LEFT join j3 ON j1.j_key=j3.j_key)
LEFT join j4 ON j1.j_key=j4.j_key)
LEFT join j5 ON j1.j_key=j5.j_key
;

Just needed the parenthesis!