-1

In SQL, is there something like "IN", but for multiple "AND" conditions instead of multiple "OR" conditions?

EDIT: I see now that this was a foolish question. What I really meant was: If I have a table like this:

_id time_entry_id tag_id

1 1 1

2 1 2

3 1 3

4 2 2

5 2 3

Is there an SQL command that will return only the time_entry_ids where tag_id = 1 AND 2 AND 3.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3302602
  • 79
  • 5
  • 4
    How can a string be `A` and `B` at the same time? That does not make sense. – juergen d Jun 05 '14 at 14:35
  • 1
    What sort of syntax would you expect from this sort of function? How do you think you would use something like `IN` to work for `AND`? – Elias Jun 05 '14 at 14:35
  • Please add a use case. Something like MSSQL's `ALL()` ? `.. where F >= ALL (subquery)` – Alex K. Jun 05 '14 at 14:40

1 Answers1

2

What you're describing is called a relational division. There is no operator in SQL for this.

There are two common solutions.

The first solution is to do a self-join for each of the values you're looking for. That way you can write an expression in the WHERE clause that references the same column and compares for three different values.

SELECT t1.time_entry_id
FROM table_like_this t1
JOIN table_like_this t2 USING (time_entry_id)
JOIN table_like_this t3 USING (time_entry_id)
WHERE t1.tag_id = 1 AND t2.tag_id = 2 AND t3.tag_id = 3;

The second solution is to find all rows with any of the values you're looking for, and if you find a group of such rows that includes all three, then the group passes your relational division search.

SELECT time_entry_id
FROM table_like_this
WHERE tag_id IN (1,2,3)
GROUP BY time_entry_id
HAVING COUNT(DISTINCT tag_id) = 3;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828