http://www.sqlite.org/lang_select.html
(1) "If there is an ON clause specified, then the ON expression is evaluated for each row of the cartesian product as a boolean expression. All rows for which the expression evaluates to false are excluded from the dataset." (2) " If the join-op is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that corresponds to no rows at all in the composite dataset (if any). The added rows contain NULL values in the columns that would normally contain values copied from the right-hand input dataset." (3) " When more than two tables are joined together as part of a FROM clause, the join operations are processed in order from left to right. In other words, the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C)." ([1,Paul], [2,John]) X ([1,1,3],[3,2,4]) =(1)=> ([1,Paul,1,1,3],[2,John,3,2,4]) (2) does not apply, as all LEFT rows have 1 corresponding result row =(3)=> ([1,Paul,1,1,3],[2,John,3,2,4]) X ([1,Box],[2,Pen],[3,Letter]) =(1)=> ([1,Paul,1,1,3,1,Box],[2,John,3,2,4,3,Letter]) (2) does not apply, as all LEFT rows have 1 corresponding result row >From which you select [Paul,Box,3] and [John,Letter,4]. The only way you are going to get more than 2 rows out of the query is to first join (no need for LEFT or OUTER) subj with itx; then you can go looking for the appx row that matches the combination. Solution 1 (subquery) select sub_descr,itx_descr,ifnull((select app_load from appx where app_ref_itx=itx_id and app_ref_sub=sub_id),0) load from itx join subj; sub_descr itx_descr load --------- --------- ---- Paul Box 3 John Box 0 Paul Pen 0 John Pen 0 Paul Letter 0 John Letter 4 Solution 2 (join): select sub_descr,itx_descr,ifnull(app_load,0) from itx join subj left outer join appx on app_ref_itx=itx_id and app_ref_sub=sub_id; sub_descr itx_descr ifnull(app_load,0) --------- --------- ------------------ Paul Box 3 John Box 0 Paul Pen 0 John Pen 0 Paul Letter 0 John Letter 4 Don't forget to add an ORDER BY clause if you want a specific order. -----Ursprüngliche Nachricht----- Von: Danilo Cicerone [mailto:cyds...@gmail.com] Gesendet: Freitag, 26. April 2013 11:40 An: SQLITE Forum Betreff: [sqlite] Left join help. Hi to all, I'm looking for a query on the following schema: PRAGMA foreign_keys=ON; BEGIN TRANSACTION; CREATE TABLE subj ( sub_id INTEGER PRIMARY KEY, -- 00 sub_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "subj" VALUES(1,'Paul'); INSERT INTO "subj" VALUES(2,'John'); CREATE TABLE itx ( itx_id INTEGER PRIMARY KEY, -- 00 itx_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "itx" VALUES(1,'Box'); INSERT INTO "itx" VALUES(2,'Pen'); INSERT INTO "itx" VALUES(3,'Letter'); CREATE TABLE appx ( app_ref_itx INTEGER DEFAULT NULL -- 00 REFERENCES itx(itx_id) ON UPDATE CASCADE ON DELETE RESTRICT, app_ref_sub INTEGER DEFAULT NULL -- 01 REFERENCES subj(sub_id) ON UPDATE CASCADE ON DELETE RESTRICT, app_load INTEGER NOT NULL DEFAULT 0, -- 02 UNIQUE(app_ref_itx, app_ref_sub) ); INSERT INTO "appx" VALUES(1,1,3); INSERT INTO "appx" VALUES(3,2,4); COMMIT; table appx stores item's(table itx) quantity load for each user (table subj). I'd to know how many items each user has: Paul|Box|3 Paul|Letter|0 Paul|Pen|0 John|Box|0 John|Letter|4 John|Pen|0 and the above is the result what I aim to!!! I tried: select sub_descr, itx_descr, app_load from subj left outer join appx on sub_id = app_ref_sub left outer join itx on app_ref_itx = itx_id; but it returns only: Paul|Box|3 John|Letter|4 Thanks in advance, Danilo _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ________________________________ Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
