Terence Lorenzo wrote:
> I have these 2 queries
>
> select K1.keyword
> from emaildata as E1
> INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique
> INNER JOIN keywords as K1 on K1.id = L1.id_keyword
> WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%'
>
> This query finds all matching keywords
>
> select
> E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset
>
> from emaildata as E1
> left join subjects on id_subject = subjects.id
> left join recipients as R1 on E1.id_from = R1.id
> left join recipients as R2 on E1.id_to = R2.id
> left join chunkinfo as C1 on E1.id_chunk = C1.id;
>
> this query gets all the info about an email
>
> Now I'd like to mix the 2 queries; I want to limit the 2nd query results
> with the 1st query, but I cant figure it out
I do that sort of thing all the time, using a subquery in the FROM clause.
Basically what you want, for example, is to make a filtering query that just
returns a single-column rowset whose values are the unique primary keys for the
table your main query is selecting on.
For example:
select ...
from (
select idcol
from footbl
<all the joins, wheres, etc for filtering go here>
) as footbl_filt
inner join footbl on footbl.idcol = footbl_filt.idcol
<the left-joins etc to bring in related info to return>
Or I suppose that subquery could go in the main WHERE instead, but I always did
it in FROM.
The point is that this approach cleanly separates the complexity for the search
filter from the complexity for gathering the details you want to return.
Complexity management is just one of several reasons why subquery support is
immensely valuable in a DBMS, and I'm glad SQLite supports it.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
