Pavlo wrote:
>> Pavlo wrote:
>>> I want to lookup small tables only when flag is set but I can't see
>>> how to tell SQLite to skip JOIN lookup for certain rows and return
>>> NULLs.
>>
>> SELECT * FROM Big LEFT JOIN Small ON Big.flag AND Big.ID = Small.BigID
>
> Sadly, you are wrong here.
> Taking your query sqlite will Look for record anyway, and flag 'false'
> doesn't stop it!
>
> Personally I can't understand why it is not so smart to see that
> Big.flag does not depend on small table and so when it equivalent to
> 'false' there can't be any row in Small to match this condition...
Apparently, SQLite always executes the join before evaluating other
conditions.
Try something like this:
SELECT *
FROM Big INNER JOIN Small ON Big.ID = Small.BigID
UNION ALL
SELECT *, NULL, NULL, ...
FROM Big
WHERE NOT flag
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
