CODEWITHSUNDEEP
mysqlsqlselectjoingroup-by
Darshan Mehta
Darshan Mehta

Reputation: 30819

Fetching data from mapping table

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?

Upvotes: 3

Views: 95

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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 |

Upvotes: 1

Related Questions