CODEWITHSUNDEEP
sqlmysql
thecore7
thecore7

Reputation: 484

Mysql query using in () doesn't work with a comma-separated value

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

Upvotes: 0

Views: 52

Answers (3)

sbrattla
sbrattla

Reputation: 5386

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".

Upvotes: 0

Barmar
Barmar

Reputation: 782181

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

Upvotes: 4

sarwar026
sarwar026

Reputation: 3821

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';

Upvotes: 1

Related Questions