It's pretty strange how you try to join with some table not even mentioning any column of that table in the joining condition. I bet behavior is not defined for such cases in SQL standard and you're getting some interpretation of such query.
Probably this query will return what you want: SELECT c1.cur cur1, c2.cur cur2, CASE WHEN c1.cur = c2.cur THEN 1 ELSE x.rate END rate FROM currency c1 CROSS JOIN currency c2 LEFT JOIN exchange x ON x.cur1=c1.cur AND x.cur2=c2.cur Pavel On Wed, Feb 24, 2010 at 3:36 PM, Mark Brand <[email protected]> wrote: > Hi, > > I've run into some puzzling behavior. I've tried to distill it to a > minimal case. In the final SELECT query below, the last LEFT JOIN clause > seems have the effect of an INNER JOIN in that its condition limits the > rows returned. I can rewrite the query to get the desired result using > a UNION or CASE, but I'm curious to understand what's going on here. > > This SQLite 3.6.22. > > regards, > > Mark > > CREATE TABLE currency ( > cur CHAR(3), > PRIMARY KEY (cur) > ); > > CREATE TABLE exchange ( > cur1 CHAR(3), > cur2 CHAR(3), > rate REAL, > PRIMARY KEY (cur1, cur2) > ); > > INSERT INTO currency (cur) VALUES ('EUR'); > INSERT INTO currency (cur) VALUES ('GBP'); > INSERT INTO currency (cur) VALUES ('USD'); > > INSERT INTO exchange (cur1, cur2, rate) VALUES ('EUR', 'GBP', 0.85); > INSERT INTO exchange (cur1, cur2, rate) VALUES ('GBP', 'EUR', 1/0.85); > > --Expected results > SELECT c1.cur cur1, c2.cur cur2, x.rate > FROM currency c1 > CROSS JOIN currency c2 > LEFT JOIN exchange x > ON x.cur1=c1.cur > AND x.cur2=c2.cur; > > /* results > EUR|EUR| > EUR|GBP|0.85 > EUR|USD| > GBP|EUR|1.17647058823529 > GBP|GBP| > GBP|USD| > USD|EUR| > USD|GBP| > USD|USD| > */ > > --Gives unexpected results > SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate, x.rate) rate > FROM currency c1 > CROSS JOIN currency c2 > LEFT JOIN exchange x > ON x.cur1=c1.cur > AND x.cur2=c2.cur > LEFT JOIN (SELECT 1 rate) self > ON c1.cur=c2.cur; > > /* results > > EUR|EUR|1 > GBP|GBP|1 > USD|USD|1 > > */ > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
