The join is valid and the results are perfectly ok. You are using LEFT JOIN, which produces a row even if there is NO MATCH on the RHS, returning NULL for fields selected from there.
SELECT * FROM C JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c a b ---------- ---------- ---------- 5 3 4 5 4 3 -----Ursprüngliche Nachricht----- Von: Rob Golsteijn [mailto:rob.golste...@mapscape.eu] Gesendet: Donnerstag, 16. Jänner 2014 11:21 An: sqlite-users@sqlite.org Betreff: [sqlite] Mutally dependent JOIN clauses Dear List, I came across a query with 2 LEFT JOINs of which the join clauses were mutually dependent. They did not produce the result I expected, but now I wonder if this is legal SQL in the first place. I created a small example which illustrates the problem. The example tries to find Pythagorean Triples (i.e. integers a, b, and c for which holds a^2 + b^2 = c^2) for given set of possible values for a, b, and c. Note: that in my query the JOIN-clause of A refers to table B that is LEFT JOINed later, and the JOIN clause of B refers back to table A. .headers on .null <null> CREATE TABLE A (a INTEGER); CREATE TABLE B (b INTEGER); CREATE TABLE C (c INTEGER); INSERT INTO C VALUES(5); INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO A VALUES(3); INSERT INTO A VALUES(4); INSERT INTO A VALUES(5); INSERT INTO B VALUES(1); INSERT INTO B VALUES(2); INSERT INTO B VALUES(3); INSERT INTO B VALUES(4); INSERT INTO B VALUES(5); SELECT * FROM C LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c; c|a|b 5|1|<null> 5|2|<null> 5|3|4 5|4|3 5|5|<null> When I look at the first result row and substitute that in the JOIN clause of A I get 1*1 + NULL*NULL= 5*5 which is definitely not true since the lhs of the expression is NULL and the rhs 25. This made me wonder which value Sqlite uses for B.b when it is LEFT JOINing table A. It is appearantly not the value that is actually used when LEFT JOINing table B. I don't know exactly what to expect from Sqlite. Either (1) an error indicating that it is illegal to refer in the JOIN clause of a LEFT JOIN to a table that is LEFT JOINed later; or (2) only the result rows c|a|b 5|3|4 5|4|3 but neither of these options is the case. Is this valid SQL that Sqlite cannot handle or is this just invalid SQL? In the latter case it would be nice if SqLite complained about it). Rob Golsteijn _______________________________________________ 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
