0

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.

  • You can use two exists subqueries or two (inner, not left) joins. – ypercubeᵀᴹ Feb 3 '16 at 7:04
  • Could you show me simple example? Because I tried using inners but it didn't work properly. =S – mesqueeb Feb 3 '16 at 7:18
  • 1
    Edit the question and add the SHOW CREATE TABLE tablename ; outputs and some sample data. – ypercubeᵀᴹ Feb 3 '16 at 7:23
0
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).

  • Thanks! But i need to check the actual values, not just how much. – mesqueeb Feb 4 '16 at 0:01
  • see edit........ – Rick James Feb 4 '16 at 0:12
0

Use a GROUP_CONCAT to make them the same line and searchable.

Say you have a three way table. that has products, categories and a link table. First show all products with their categories as one table:

SELECT * FROM products
LEFT JOIN category_relations ON products.id = category_relations.prod_id
LEFT JOIN categories ON category_relations.cat_id = categories.id

Result:

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

Then let's make the category 1 line with GROUP_CONCAT:

SELECT *, GROUP_CONCAT(category SEPARATOR ', ')
FROM products
LEFT JOIN category_relations ON products.id = category_relations.prod_id
LEFT JOIN categories ON category_relations.cat_id = categories.id
GROUP BY products.id

Then the table will look like:

Name     | GROUP_CONCAT(category)
----------------
Cool cap | clothes, hats

However you cannot use WHERE to search a group_concat! You need to use 'HAVING' to search in a group_concat:

SELECT *, GROUP_CONCAT(category SEPARATOR ', ') AS allcategories
FROM products
LEFT JOIN category_relations ON products.id = category_relations.prod_id
LEFT JOIN categories ON category_relations.cat_id = categories.id
GROUP BY products.id
HAVING allcategories LIKE '%clothes%'
AND allcategories LIKE '%hats%'

This query will only show us the products with BOTH these categories. And will not show the products with just either one category.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.