mardi 21 avril 2015

SQL/SQLite: Left join only if multiple columns match, select fields for the join result

I need to left-join two tables. The left one has about 1500 entries. These tables have three columns that must match for a correct join. I can't get it to work, though. Adding all three tables with AND produces incorrect results; a WHERE clause (after the join) doesn't return a full left join.

Furthermore, I need to keep only the three IDs and "someinfo". How would I accomplish that? Adding the field in the select produces millions of extra results. Do I have to perform the join first and then query the result?

-- unexpected results, triple joins, too many results

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2 
     ON t1.id_1 = t2.id_1 
     AND t1.id_2 = t2.id_2 
     AND t1.id_3 = t2.id_3

-- millions of results

SELECT t1.*, t2.someinfo
FROM Table1 t1
LEFT JOIN Table2 t2 
     ON t1.id_1 = t2.id_1 
     AND t1.id_2 = t2.id_2 
     AND t1.id_3 = t2.id_3

Tables:

enter image description here

Aucun commentaire:

Enregistrer un commentaire