Is there something in MySQL like IN but that uses AND instead of OR?

Question

I need an SQL statement to retrieve records where the key (or any column) is in the associated table, for example:

documentId termId 4 1 4 2 3 3 5 1 

It:

 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 termid values ​​are 1 and 2 and 3? Like IN, but with AND.

Answer 1

There is no direct 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 should use HAVING COUNT(DISTINCT , because duplicate termid equal to 2 for the same document file will be false. And COUNT should 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 it can be a pain to handle criteria that change a lot.