Mark Brand <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
