MySQL only queries rows containing a specific value after concatenation
I am trying to do something, it basically boils down to this: I want to get all products and show all the categories this product is in. But then I want to filter only those products that exist in the x and y categories.
So this is my request:
SELECT p.id, p.name,GROUP_CONCAT(distinct(pc.category_id) SEPARATOR ", ") as category
FROM products p
LEFT JOIN product_category pc ON p.id = pc.productid
GROUP BY p.id;
This works great, I get the result like this:
p.id | p.name | category
10 | example| 15,16,17
11 | example| 15,20
12 | example| 39,40
Obviously, "15, 16,16" are the categories the product is in. However, now I want to filter the result set on products containing only category 15 or 16. Thus, I want to get the result:
p.id | p.name | category
10 | example| 15,16,17
11 | example| 15,20
So, I tried to add WHERE to my MySQL statement like this:
WHERE category IN (15,16)
This works for filtering, but the problem is that in the result set I cannot see which other categories are also in the product. Thus, I see the following:
p.id | p.name | category
10 | example| 15,16
11 | example| 15
Note the difference with the desired result: I just see the filtered cats, not all the cats.
I understand why this behaves the way it is, since obviously the "category" column in my result set is based on the values after filtering. However, I don't know how to get around this, or if what I want is possibly even.
PS: this query will work on huge databases, so the faster the query the better.
source to share
One (admittedly weird) option is to use find_in_set
cumulatively:
SELECT p.id,
p.name,
GROUP_CONCAT(DISTINCT(pc.category_id) SEPARATOR ", ") AS category
FROM products p
LEFT JOIN product_category pc ON p.id = pc.productid
GROUP BY p.id
HAVING FIND_IN_SET('15', GROUP_CONCAT (pc.category_id)) > 0 OR
FIND_IN_SET('16', GROUP_CONCAT (pc.category_id)) > 0
source to share
Try this untested query:
select * p.id, p.name,GROUP_CONCAT(distinct(pc.category_id) SEPARATOR ", ") as category
from FROM products p
LEFT JOIN product_category pc ON p.id = pc.productid
where p.id in (
SELECT pc.productid
product_category pc ON p.id = pc.productid
where category IN (15,16)
GROUP BY pc.productid
)
GROUP BY p.id
source to share
I was just wondering how I would solve this problem:
SET @needle = '15,16';
SELECT p.id, p.name, GROUP_CONCAT(pc.category_id SEPARATOR ', ') AS 'category' FROM products p
LEFT JOIN product_category pc ON p.id = pc.productid GROUP BY id
HAVING REGEXP_INSTR(GROUP_CONCAT(pc.category_id), CONCAT('([[:<:]])(',REPLACE(@needle,',','|'),')([[:>:]])'));
Groups before going through the HAVING offer:
p.id | p.name | category
10 | example| 15,16,17
11 | example| 15,20
12 | example| 39,40
We then check the groups using the HAVING clause and REGEXP:
HAVING REGEXP_INSTR(t3.category, CONCAT('[[:<:]](15|16)[[:>:]]'))
/* REGEX: START_WORD_BOUNDARY(15 OR 16)END_WORD_BOUNDARY */
And here it is:
p.id | p.name | category
10 | example| 15,16,17
11 | example| 15,20
source to share