Jens Törnell Jens Törnell - 1 month ago 6
MySQL Question

Mysql group by with multiple columns with inner joins

I have 3 tables,

products
,
productscategories
and
categories
.

The
productscategories
is a many to many table with only id numbers of the other two tables.

The result without the group by looks like this:

id | Url | Category
-------------------------------------
1 | http://example.com/12 | hat
2 | http://example.com/12 | shoe
3 | http://example.com/13 | hat
4 | http://example.com/13 | jacket
5 | http://example.com/14 | hat
6 | http://example.com/14 | socks


Now I want to exclude every row with the same
url
if it contains any of the choosen categories, in this case
jacket
and
shoe
.

The unwanted result looks like this:

id | Url | Category
-------------------------------------
1 | http://example.com/12 | hat
3 | http://example.com/13 | hat
5 | http://example.com/14 | hat


Because
url
with id 13 includes
jacket
I don't want it there. Same goes for
url
with 14 which includes
shoe
.

This accur because I have multiple categories and multiple urls that are not aware of each other.

The sql for the above:

SELECT * FROM products
JOIN productscategories ON products.id = productscategories.product_id
JOIN categories ON categories.id = productscategories.category_id
WHERE categories.slug NOT IN (
'shoe',
'jacket',
)
GROUP BY products.image_url


The wanted result:

id | Url | Category
-------------------------------------
5 | http://example.com/14 | hat


How can I make an sql query that makes url aware of the category, like above?

Answer
SELECT * FROM products
  JOIN productscategories ON products.id = productscategories.product_id
  JOIN categories ON categories.id = productscategories.category_id
 GROUP BY products.image_url
HAVING sum( categories.slug IN('shoe','jacket') )=0

categories.slug IN('shoe','jacket') - return 1 if category in set or 0 if not. sum() - return count of shoe/jacket in group. HAVING filter group with shoe/jacket in it.

Comments