Fetching data from mapping table

Question

I have a table structure like this:

bundle_id|service_id 

8|1

8|2

8|3

9|1

9|4

10|1

10|2

10|3

10|4

10|5

Now, I want to query the table to fetch the optimal bundle which has particular set of services.

E.g. If I want to fetch the bundle having services 1,2 and 3 then it should return 8 (and not 10). Can anyone please help me with the query?

Answer 1

Try this:

SELECT bundle_id 
FROM mappings
WHERE service_id IN (1, 2, 3)
GROUP BY bundle_id HAVING COUNT(DISTINCT service_id) = 3
ORDER BY bundle_id LIMIT 1;

EDIT

SELECT m.bundle_id  
FROM mappings m 
INNER JOIN (SELECT bundle_id, COUNT(DISTINCT service_id) serviceIdCnt 
            FROM mappings GROUP BY bundle_id
          ) A ON m.bundle_id = A.bundle_id 
WHERE m.service_id IN (1, 2, 3)
GROUP BY m.bundle_id HAVING COUNT(DISTINCT m.service_id) = 3
ORDER BY A.serviceIdCnt LIMIT 1;

Check the SQL FIDDLE DEMO

OUTPUT

| BUNDLE_ID |
|-----------|
|         8 |