Multiple left joins to single table

Question

I have a whole bunch of tables:

Table1: PersonID, A1, A2, A3, ...

Table2: PersonID, B1, B2, B3, ...

Table3: PersonID, C1, C2, C3, ...

Table4: PersonID, D1, D2, D3, ...

...

Table1 is special as it does in fact contain all individuals (names and personal information) but all other tables are almost certainly missing rows (a person simply did not participate in that event).

What I would like to do is join all the tables together (by the common PersonID) such that any missing attributes will simply be left blank. So, of course, I thought to use outer joins. This is what I tried:

SELECT * 

FROM Table1, Table2, Table3, Table4, Table5, Table6

LEFT OUTER JOIN Table2 ON Table1.PersonID = Table2.PersonID
LEFT OUTER JOIN Table3 ON Table1.PersonID = Table3.PersonID
LEFT OUTER JOIN Table4 ON Table1.PersonID = Table4.PersonID
LEFT OUTER JOIN Table5 ON Table1.PersonID = Table5.PersonID
LEFT OUTER JOIN Table6 ON Table1.PersonID = Table6.PersonID

All this does is give me a memory overload. I think I'm somehow taking the Cartesian product of all my tables and it's choking everything up. Is there a good way to do this?

Thanks in advance!

score 2 · Answer 1 · answered Mar 12 '13 at 21:46

2

Use this:

SELECT * 
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.PersonID = Table2.PersonID
LEFT OUTER JOIN Table3 ON Table1.PersonID = Table3.PersonID
LEFT OUTER JOIN Table4 ON Table1.PersonID = Table4.PersonID
LEFT OUTER JOIN Table5 ON Table1.PersonID = Table5.PersonID
LEFT OUTER JOIN Table6 ON Table1.PersonID = Table6.PersonID

Look at the SQL Joins form more info.

answered Mar 12 '13 at 21:46

Hamlet Hakobyan

32,965
6
52
68

You guys are crazy quick. Thanks for the help! – mstobb Mar 12 '13 at 22:05

score 1 · Accepted Answer · answered Mar 12 '13 at 21:48

1

Change this:

SELECT * 

FROM Table1, Table2, Table3, Table4, Table5, Table6

LEFT OUTER JOIN Table2 ON Table1.PersonID = Table2.PersonID
LEFT OUTER JOIN Table3 ON Table1.PersonID = Table3.PersonID
LEFT OUTER JOIN Table4 ON Table1.PersonID = Table4.PersonID
LEFT OUTER JOIN Table5 ON Table1.PersonID = Table5.PersonID
LEFT OUTER JOIN Table6 ON Table1.PersonID = Table6.PersonID

to this:

SELECT * 

FROM Table1
LEFT OUTER JOIN Table2 ON Table1.PersonID = Table2.PersonID
LEFT OUTER JOIN Table3 ON Table1.PersonID = Table3.PersonID
LEFT OUTER JOIN Table4 ON Table1.PersonID = Table4.PersonID
LEFT OUTER JOIN Table5 ON Table1.PersonID = Table5.PersonID
LEFT OUTER JOIN Table6 ON Table1.PersonID = Table6.PersonID

Yes, yo were taking the Cartesian product of all tables, and then left joining it to the others

answered Mar 12 '13 at 21:48

Pieter Geerkens

11,775
2
32
52

Such a simple thing.... Thanks for the quick response! Well, it's almost working perfectly now, problem is I'm not getting the same number of rows in the output as I expected. Table 1 has approximately 1000 rows, so since everything is being left joined to it, the result too should have 1000 rows, but I'm only getting about 900 rows. It's very possible that there are ~100 people who participated in nothing, but I would think then that every field would just be a NULL. Is this not the case? – mstobb Mar 12 '13 at 22:04
Can you provide an example of a row that is being omitted. – Pieter Geerkens Mar 12 '13 at 22:13
I think this new problem is software related. Libre Base is less than perfect in many ways. When I count the number of rows directly with a new query, I get the right number. Very odd. Thanks for the help! – mstobb Mar 12 '13 at 23:45

Multiple left joins to single table

2 Answers2