left join trouble

[djedery]

Hi all -

I have a three table situation; table on the 'left' (table1) always has data, table on the 'right' (table2) may not always have data. Between those two tables is a many-to-many table (tableX) containing only IDs from the tables on the left and right.

I had thought I could handle this problem with a single LEFT JOIN but I guess not. Can anyone tell me how to fix the following SQL?

"SELECT ... FROM table1
LEFT JOIN tableX, table2
ON table1.id=tableX.1id AND table2.id=tableX.2id
WHERE ..."

Could it be done with multiple LEFT JOINs? Ie..

"SELECT ... FROM table1
LEFT JOIN tableX ON table1.id=tableX.1id
LEFT JOIN table2 ON table2.id=tableX.2id
WHERE ..."

Or would an INNER JOIN come into play somewhere? Ugh.

Thanks for any help in advance. 🙂

[ahundiak]

Using two left joins will work.

Starting your column names with a number (2id) is legal but could cause problems down the line.

[djedery]

Using two left joins will work.

Great! Thanks. I had worried that it would not because records are optional in table2, but if there are records in table2, they must be matched to table1. I don't fully understand how LEFT JOINS work, yet.

Starting your column names with a number (2id) is legal but
could cause problems down the line

Oh, that was just for example purposes. 🙂 Thanks for the warning tho.