"Igor Tandetnik" <itandet...@mvps.org> wrote in message news:hm45gu$s5...@dough.gmane.org... > 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
One little change makes it work: 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 LIKE c2.cur; Don't ask me why '=' and 'LIKE' should behave differently in this example. Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users