Mark Brand <mabr...@mabrand.nl> wrote: > --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 > > */
Yes, looks like a bug to me. I see no reason why it shouldn't work. LEFT JOIN should never produce fewer rows than what left-hand-side table contains. As a workaround, try this instead: SELECT c1.cur cur1, c2.cur cur2, (case when c1.cur=c2.cur then 1 else x.rate) rate FROM currency c1 CROSS JOIN currency c2 LEFT JOIN exchange x ON x.cur1=c1.cur AND x.cur2=c2.cur; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users