2

I am facing one problem for creating one sql query. Actually i want to check if wherein clause Exists all elements or not, If all elements exists in WhereIn clause then i want to return that records with groupby.. Here is my one table produt_categories :-

id product_id  category_id
 1    1              1
 2    1              2

Sow when i apply Wherein Like below:-

SELECT * FROM `product_categories` 
WHERE  category_id in (1,2,3) group by(product_id);

Now it return me this result:-

id product_id  category_id
 1    1              1

Actually its returning me fine because actual behavior of whereIn clause. So i want if all category_id 1,2,3 exists then it must show records. If i pass 1,2 then i will also come. Hope you guys understand.Please help me to create qyuery

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
kunal
  • 255
  • 3
  • 17

1 Answers1

1

You could use HAVING:

SELECT * 
FROM `product_categories` 
WHERE  category_id in (1,2,3) 
group by(product_id)
HAVING COUNT(DISTINCT category_id) = 3;  -- #elements from `IN` clause

SELECT * FROM tab GROUP BY col is an anti-pattern.

Related article: Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275