Mysql – How to check if a record is linked with two or more values in a left join

existsMySQL

In MySQL, I have a table of products connected with a different table with categories.

I often show the list by making a LEFT JOIN and GROUPING the results:

e.g. LEFT JOIN gives me:

Name | Category
----------------
Cool cap | clothes
Cool cap | hats

Then GROUPING by ID gives me 1 result.

The question: How can I show a list of products with category='clothes' AND category='hats'? Because the item won't show up because each category shows on a new line.

Best Answer

GROUP BY Name
HAVING COUNT(*) >= 2

You may need to do something like this:

WHERE ...
  AND ( SELECT COUNT(*)
           FROM tbl
           WHERE Name = ...
           GROUP BY Name
           HAVING COUNT(*) >= 2 )

For checking for exactly a certain list:

           WHERE Category IN ('clothes', 'hats')
           GROUP BY Name, Category
           HAVING COUNT(*) = 2

(There can't be more than 2 after the GROUP BY; this confirms that both are included.)

           WHERE Category IN ('clothes', 'hats', 'scarves')
           ...
           HAVING COUNT(*) >= 2

would check for any 2 of those 3 (or all 3).