5

I need a SQL statement to retrieve records where it key (or any column) is in a associate table, 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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Skatox
  • 4,237
  • 12
  • 42
  • 47

1 Answers1

7

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • For those wondering about the performance difference between these two methods, have a [look at this answer here.](http://stackoverflow.com/a/31102803/4248167) In summary, the join method puts less strain on the database. – true Jun 28 '15 at 20:09