Re: [sqlite] Filtering a join
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='')
>
> 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='')
> So for every I need to exclude, I add another AND NOT filter.
In either form of the subquery, you could write:
... AND value IN ('', '', ...)
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering a join
> 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='') > WHERE r.guid_id=1 >AND r.status IS NULL > ORDER BY foo > > > It think you can simply add it to the ON-clause... That excludes just the single row from the related set which than produces a join with the offending record in table A and an incomplete set from table B. What I had will work, I just think its rather ugly. Thanks for all the help, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering a join
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='') ORDER BY foo So for every 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='') 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
Re: [sqlite] Filtering a join
> 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='') ORDER BY foo So for every 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering a join
Joseph L. Casale wrote: > I have three tables where table A is a left joined one to many relationship > against > two other tables. I now need to modify this to accept filtering what is > returned > from table A based on one of the many rows in table B and/or C. This description is extremely vague. > The row from table A is only valid with all the corresponding rows from B and > C. You just said "one of the many rows". > What is the most elegant way to accomplish this? With a query. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filtering a join
On 31-01-2014 17:23, Joseph L. Casale wrote: I have three tables where table A is a left joined one to many relationship against two other tables. I now need to modify this to accept filtering what is returned from table A based on one of the many rows in table B and/or C. The row from table A is only valid with all the corresponding rows from B and C. What is the most elegant way to accomplish this? Thanks for any pointers, use a 'inner join', in stead of a 'left join' ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
