I have a table with such structure:
| id | product | company |
|---|---|---|
| 1 | table | A |
| 2 | table | B |
| 3 | table | C |
| 4 | chair | A |
| 5 | chair | B |
| 6 | sofa | A |
| 7 | sofa | C |
I need to find all products whose companies include both A and B. In the example above these would be table and chair.
I managed to write a query that will return products that have more than one companies:
SELECT product FROM table_name
GROUP BY product HAVING COUNT(*) > 1
order by COUNT(*) desc;
But I can’t make a selection taking into account certain companies, and not just their number.
I would be grateful for any help!
>Solution :
SELECT product
FROM table_name
WHERE company IN ('A', 'B')
GROUP BY product
HAVING COUNT(DISTINCT company) = 2
ORDER BY COUNT(*) desc;