Re: [sqlite] Referencing subquery several times

On 13 Nov 2011, at 1:47am, Alexandre Courbot wrote: > On Sat, Nov 12, 2011 at 11:59 PM, Black, Michael (IS) > wrote: >> Why are you contorting yourself into just one query? > > It is actually part of a larger query that joins against this result. > Granted, there are

Re: [sqlite] Referencing subquery several times

On Sat, Nov 12, 2011 at 11:59 PM, Black, Michael (IS) wrote: > Why are you contorting yourself into just one query? It is actually part of a larger query that joins against this result. Granted, there are ways to workaround this, but I would be surprised if it was not

Re: [sqlite] Referencing subquery several times

On 12-11-2011 19:13, Darren Duncan wrote: > Alexandre Courbot wrote: >> Hi everybody, >> >> Here is a simplified version of the statement I try to run (let a, b, >> and m be tables with only one column named c containing integers): >> >> SELECT * FROM m WHERE >> c IN (SELECT * FROM a) OR >>

Re: [sqlite] Referencing subquery several times

Alexandre Courbot wrote: Hi everybody, Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT * FROM a)

Re: [sqlite] Referencing subquery several times

Why are you contorting yourself into just one query? Your last clause would be a complete table scan seems to me. Sounds slow versus 2 queries. Why can't you just do this? (pseudo code here) -- no table scans involved at all. select docid from b where t match 'blah'; if (rowcount > 0) // the

Re: [sqlite] Referencing subquery several times

On Sat, Nov 12, 2011 at 10:02 PM, Luuk wrote: > Should give same results as: > > SELECT * FROM m WHERE >    c IN (SELECT * FROM a) OR >    c IN (SELECT * FROM b) >    AND (c IN (SELECT * FROM b)); > > Because of the 'OR' on the second line > > This can be simplified to: > >

Re: [sqlite] Referencing subquery several times

On 12-11-2011 12:12, Alexandre Courbot wrote: > Hi Igor, > > On Nov 12, 2011 11:45 AM, "Igor Tandetnik" wrote: >> This query doesn't make much sense. It appears that quite a few > conditions are redundant, or else the parentheses are in the wrong places. > What logic were

Re: [sqlite] Referencing subquery several times

On 12-11-2011 03:43, Igor Tandetnik wrote: > On 11/11/2011 9:24 PM, Alexandre Courbot wrote: >> Here is a simplified version of the statement I try to run (let a, b, >> and m be tables with only one column named c containing integers): >> >> SELECT * FROM m WHERE >> c IN (SELECT * FROM a) OR

Re: [sqlite] Referencing subquery several times

Hi Igor, On Nov 12, 2011 11:45 AM, "Igor Tandetnik" wrote: > This query doesn't make much sense. It appears that quite a few conditions are redundant, or else the parentheses are in the wrong places. What logic were you trying to express here? Sorry, I tried to simplify it

Re: [sqlite] Referencing subquery several times

On 11/11/2011 9:24 PM, Alexandre Courbot wrote: Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT *

Re: [sqlite] Referencing subquery several times

> I know this could be done more easily, but I have to keep this > structure (i.e. cannot JOIN for instance). My question is, how could I > avoid repeating the subqueries after the AND NOT, since they will > return the same set as the previous ones? With your restriction on query structure you

[sqlite] Referencing subquery several times

Hi everybody, Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT * FROM a) OR c IN (SELECT *