Joseph L. Casale wrote:
> SELECT
> r.id AS foo ...
> , a.value AS a_value ...
> , t.value AS t_value
> FROM request r
> LEFT JOIN attribute a
> ON a.req_id=r.id
> LEFT JOIN action t
> ON t.req_id=r.id
> WHERE ... NOT r.id IN (SELECT DISTINCT(req_id) FROM action WHERE
> key='something' AND value='XXXX')
>
> Is there a way to avoid the nested select?
The decision whether to return a particular record depends on the values
in some other records. This is not possible without a subquery.
You could try writing the filter as a correlated subquery, but you have
to measure whether this makes any positive difference:
WHERE ... NOT EXISTS (SELECT 1 FROM action WHERE req_id = r.id AND
key='something' AND value='XXXX')
> So for every XXXX I need to exclude, I add another AND NOT filter.
In either form of the subquery, you could write:
... AND value IN ('XXXX', 'YYYY', ...)
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
