"Igor Tandetnik" <[email protected]> wrote in message
news:[email protected]...
> 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
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
