Mr.Noob Mr.Noob - 5 months ago 8
MySQL Question

MySql help to write a query

Suppose i have a table with 3 columns like below

------------------------------
|id | recipe_id | sub_category |
------------------------------
| 1 | 80 | 2 |
| 2 | 80 | 6 |
| 3 | 80 | 3 |
| 4 | 82 | 1 |
| 5 | 83 | 2 |
| 6 | 85 | 5 |
| 7 | 80 | 10 |
------------------------------


How can i get the recipe id which contains both sub categories 2 and 6 ??
Is it possible to get it using mysql query?

Answer

Try this:

SELECT recipe_id
FROM mytable
WHERE sub_category IN (2, 6)
GROUP BY recipe_id
HAVING COUNT(DISTINCT sub_category) = 2

Demo here