CODEWITHSUNDEEP
javamysqlhibernate
sidd.v29
sidd.v29

Reputation: 83

pass a list to be matched in where/having clause

MySQL table looks like

product_id    material
----------------------
1             A
1             B
1             C
2             A
2             B
3             A
3             B
3             C
3             D
4             A
4             B
4             D

I want all those product_ids which have material A,B,C

Hence output should be 1, 3

Using group by seem appropriate, but how to pass multiple material to be matched.

The number of materials to be matched can vary, i.e. 1st query maybe for material A,B,C
output : 1,3
and 2nd query might be for material A,D
output : 3,4

What query should I write in Hibernate in java program, and a mysql query as well?

Upvotes: 2

Views: 192

Answers (1)

Ullas
Ullas

Reputation: 11556

Query

SELECT product_id 
FROM products
WHERE material IN ('A', 'B', 'C')
GROUP BY product_id
HAVING COUNT(DISTINCT material) = 3;

SQL Fiddle Demo

Upvotes: 5

Related Questions