MySQL - exactly match multiple IDs in the same column

Question

Consider the following data. This table, t1, maps substance identifiers (substance_id) to corresponding filters (filter_id). The id column is an auto-incrementing PK.

+---------+--------------+-----------+
| id      | substance_id | filter_id |
+---------+--------------+-----------+
| 7892022 |           26 |      2681 |
| 7892021 |          750 |      2680 |
| 7892020 |          750 |      2679 |
| 7892019 |          750 |      2677 |
| 7892018 |          750 |      2676 |
| 7892017 |          300 |      2680 |
| 7892016 |          300 |      2679 |
| 7892015 |           20 |      2681 |
| 7892014 |           20 |      2677 |
| 7892013 |            5 |      2681 |
| 7892012 |            5 |      2680 |
| 7892011 |            5 |      2679 |
| 7892010 |            5 |      2678 |
| 7892009 |            5 |      2677 |
| 7892008 |            5 |      2676 |

I'm trying to write a query which returns the substance_id when an exact set of filter_id's are provided.

I can't use IN() to do this because that will match any of the filter_ids provided which isn't what I want to do. I've shown an example of this later.

In the sample data above the only substance_id which is on all 3 of these filter_ids

• 2676
• 2677
• 2678

is substance_id 5.

If I write this SQL

SELECT * FROM t1 WHERE filter_id = 2676 AND filter_id = 2677 AND filter_id = 2678;

It returns 0 rows.

The following SQL returns 2 rows. However, this isn't what I'm looking for because substance_id 750 is not on all 3 of the filter_ids specified.

SELECT * FROM t1 WHERE filter_id = 2676 AND 2677 AND 2678;
+---------+--------------+-----------+
| id      | substance_id | filter_id |
+---------+--------------+-----------+
| 7892008 |            5 |      2676 |
| 7892018 |          750 |      2676 |
+---------+--------------+-----------+
2 rows in set (0.01 sec)

If I were to use IN I'll get 6 rows, which definitely isn't what I'm looking for:

SELECT * FROM t1 WHERE filter_id IN(2676, 2677, 2678);
+---------+--------------+-----------+
| id      | substance_id | filter_id |
+---------+--------------+-----------+
| 7892008 |            5 |      2676 |
| 7892018 |          750 |      2676 |
| 7892009 |            5 |      2677 |
| 7892014 |           20 |      2677 |
| 7892019 |          750 |      2677 |
| 7892010 |            5 |      2678 |
+---------+--------------+-----------+
6 rows in set (0.00 sec)

I understand that the IN() condition above isn't the right approach because it will match any of the filter_ids specified.

I don't understand how to write an AND condition which matches exactly the 3 filter_ids listed earlier and nothing else (e.g. partial matches where the substance is on 2 of the 3 filters should be excluded).

Is this possible in MySQL 8?

Answer 1

A simple having count would do the trick

select substance_id 
from t1
where filter_id in (2676,2677,2678)
group by substance_id
having count(distinct filter_id) = 3;

See example