I need a SQL statement to retrieve records where it key (or any column) is in a associate tabl开发者_开发知识库e, for example:
documentId termId
4 1
4 2
3 3
5 1
This:
SELECT documentId
FROM table
WHERE termId IN (1,2,3)
...will retrieve any documentid
value where the termid
value is 1 or 2 or 3.
Is there something like this but return documentid
values where the termid
values are 1 and 2 and 3? Like an IN but with AND.
There's no straight forward functionality, but there are two options:
Using GROUP BY/HAVING
SELECT t.documentid
FROM TABLE t
WHERE t.termid IN (1,2,3)
GROUP BY t.documentid
HAVING COUNT(DISINCT t.termid) = 3
The caveat is that you have to use HAVING COUNT(DISTINCT
because duplicates of termid being 2 for the same documentid would be a false positive. And the COUNT has to equal the number of termid values in the IN clause.
Using JOINs
SELECT t.documentid
FROM TABLE t
JOIN TABLE x ON x.termid = t.termid
AND x.termid = 1
JOIN TABLE y ON y.termid = t.termid
AND y.termid = 2
JOIN TABLE z ON z.termid = t.termid
AND z.termid = 3
But this one can be a pain for handling criteria that changes a lot.
精彩评论