Terence Lorenzo wrote: > 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%'
The second test is redundant. Everything that matches '%word2%' would also match '%word%' > 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 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 where E1.id_unique in ( select L1.id_unique from keylocations AS L1 INNER JOIN keywords as K1 on K1.id = L1.id_keyword WHERE K1.keyword LIKE '%word%' ); Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
