I am trying to find all products that are connected with category list with this query:
SELECT p.id, product_name, added_date
FROM products p, products_to_categories ptc
WHERE ptc.category_id in ("89,88,83,87,84,85,86,82")
AND ptc.product_id=p.id and p.active="1"
group by p.id
if I cut the condition AND ptc.product_id=p.id returns rows - wrong rows for active condition..
what is the correct way to get correct information with 1 query? - is it possible at all?
thanks
Don't quote the list of category IDs:
SELECT p.id, product_name, added_date
FROM products p
JOIN products_to_categories ptc ON ptc.product_id=p.id
WHERE ptc.category_id IN (89, 88, 83, 87, 84, 85, 86, 82)
AND p.active="1"
GROUP BY p.id
Try removing quoting in IN clause and in p.active if active is int/bool
$q='SELECT p.id, product_name, added_date
FROM products p, products_to_categories ptc
WHERE ptc.category_id in (89,88,83,87,84,85,86,82)
AND ptc.product_id=p.id and p.active=1
group by p.id';
Dont encapsulate all categories with brackets. MySQL interprets this as a single category. Encapsulate each category, e.g. "89","90","91". You can also just skip the brackets altogether as it is integers you query on.
You would encapsulate the ids if they were strings, e.g. "one","two","three".
datatypeofactivecolumn?