Actually "CROSS" just forces SQLite to use the tables in the order specified. The result sets are identical, except for ordering and/or speed. It is the LEFT join that creates the rows where there is no match on the right hand side.
explain query plan 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; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE itx (~1000000 rows) 0 1 1 SCAN TABLE subj (~1000000 rows) 0 2 2 SEARCH TABLE appx USING INDEX sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows) explain query plan SELECT sub_descr, ...> itx_descr, ...> IFNULL(app_load, 0) ...> FROM subj ...> CROSS JOIN itx ...> LEFT JOIN appx ON sub_id = app_ref_sub AND ...> itx_id = app_ref_itx ...> ; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE subj (~1000000 rows) 0 1 1 SCAN TABLE itx (~1000000 rows) 0 2 2 SEARCH TABLE appx USING INDEX sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows) -----Ursprüngliche Nachricht----- Von: Clemens Ladisch [mailto:clem...@ladisch.de] Gesendet: Freitag, 26. April 2013 12:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Left join help. Danilo Cicerone wrote: > 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 > > 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 Your first LEFT includes users that do not have any items. Your second LEFT includes quantity loads for which no item exists. What you actually want is all combinations of users and items. This is a cross join: SELECT sub_descr, itx_descr, IFNULL(app_load, 0) FROM subj CROSS JOIN itx LEFT JOIN appx ON sub_id = app_ref_sub AND itx_id = app_ref_itx Regards, Clemens _______________________________________________ sqlite-users mailing list 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
