Search within same ID across several rows

Question

My table has the following structure:

+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1  | 1       | 15333      | 1           |
| 2  | 1       | 12355      | 1           |
| 3  | 1       | 768        | 1           |
| 4  | 2       | 12455      | 1           |
| 5  | 2       | 9581       | 1           |
| 6  | 3       | 41         | 1           |
| 7  | 4       | 125        | 1           |
| 8  | 1       | 17854      | 2           |
| 9  | 2       | 35214      | 2           |
| 10 | 3       | 12455      | 2           |
| 11 | 3       | 988        | 2           |
+----+---------+------------+-------------+

I have a list of meaning_ids and I want to get all the sentence_ids that contain all of the meaning_ids. I do not care what the sentence it is, as long as the sentence contains all meaning_ids (= sentence_id is the same).

Lets say I have the following sentence_ids: 12455 and 15333. The query should return sentence_id 1 because both only occur there.

How can I achieve this with MySQL?

Answer 1

This requirement is known as relational division. One popular approach is

SELECT sentence_id
FROM   YourTable
WHERE  meaning_id IN ( 12455, 15333 )
GROUP  BY sentence_id
HAVING COUNT(DISTINCT meaning_id) = 2 

SQL Fiddle

If your table has a unique constraint on sentence_id,meaning_id you can drop the DISTINCT in the query above.