On 31-01-2014 18:26, Joseph L. Casale wrote:
use a 'inner join', in stead of a 'left join' ?
Hi Luuk,
Sorry for the ambiguity, let me clarify.
Table A yields individual rows that I am interested in. For each of these rows,
a one to many exists with table B and or C.
In this case, an inner join wont work as valid rows from table A don't require
rows from B or C.
However, if an FK ref in B or C exists for a row in A, then *all* of those
related rows
in B or C must accompany the set.
I need to implement a filter where I may be given one or more column values that
may exist in table B or C. Since the schema requires the ref to A within B or
C, that
data set (n rows) from B or C might need to exclude the row from A.
For example:
SELECT
r.id AS foo
, r.col_a
, r.col_b
, a.name AS a_name
, a.value AS a_value
, t.res_id AS t_res_id
, t.key AS t_key
, 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 r.guid_id=1
AND r.status IS NULL
AND NOT r.id IN (SELECT DISTINCT(req_id) FROM action WHERE
key='something' AND value='XXXX')
ORDER BY foo
So for every XXXX I need to exclude, I add another AND NOT filter. Is there a
way to avoid the nested select?
Thanks for the patience guys,
jlc
SELECT
r.id AS foo
, r.col_a
, r.col_b
, a.name AS a_name
, a.value AS a_value
, t.res_id AS t_res_id
, t.key AS t_key
, 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 AND NOT(t.key='something' AND t.value='XXXX')
WHERE r.guid_id=1
AND r.status IS NULL
ORDER BY foo
It think you can simply add it to the ON-clause...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
