Given the following (very simplified) mysql table structure:
products
- id
product_categories
- id
- product_id
- status (integer)
product_tags
- id
- product_id
- some_other_numeric_value
I am trying to find every product that has an association to a certain product_tag, and that a relation to at least one category whichs status-attribute is 1.
I tried the following query:
SELECT *
FROM `product` p
JOIN `product_categories` pc
ON p.`product_id` = pc.`product_id`
JOIN `product_tags` pt
ON p.`product_id` = pt.`product_id`
WHERE pt.`some_value` = 'some comparison value'
GROUP BY p.`product_id`
HAVING SUM( pc.`status` ) > 0
ORDER BY SUM( pt.`some_other_numeric_value` ) DESC
Now my problem is: The SUM(pt.some_other_numeric_value)
returns unexpected values.
I realized that if the product in question has more then one relation to the product_categories table, then every relation to the product_tags table is counted as many timed as there are relations to the product_categories table!
For example: If product with id=1 has a relation to product_categories with ids = 2, 3 and 4, and a relation with the product_tags with ids 5 and 6 - then if I insert a GROUP_CONCAT(pt.id)
, then it does give 5,6,5,6,5,6 instead of the expected 5,6.
At first I suspected it was a problem with the join type (left join, right join, inner join, and so on), so I tried every join type that I know of, but to no avail. I also tried to include more id-fields into the GROUP BY
clause, but this didn´t solve the problem either.
Can somebody explain to me what is actually going wrong here?