Gelid Gelid - 6 months ago 15
SQL Question

MYSQL filter multiply row by two lists of id

I have results:

item_id subitem_id
----------------------
1 35
1 25
1 8
2 10
2 25
3 60
4 35
4 25
4 44
5 1
5 23
5 15
5 13
5 9


and I have two lists of subitem

(25,44,1)
(8,9)


how do I set the where clause in order to filter the result and return this

item_id subitem_id
----------------------
1 35
1 25 <-- first set
1 8 <-- second set
-----------------
5 1 <-- first set
5 23
5 15
5 13
5 9 <-- second set


because this
item_id
contain both
subitem_id
from two lists

SELECT
`item_id`
FROM table
WHERE `subitem_id` in (25,44,1)
AND `subitem_id` in (8,9)


Did not work, because in one time
subitem_id
have one id (not all list)

P.S.
This is a simple example, in reality we have more than 100k records with some join construction

Answer

SQL Fiddle

I think you're trying to make sure a item_ID has subcategories in 2 differen sets..

Select * from table A
where exists (Select 1 from table B where A.Item_Id = B.Item_ID and subitem_ID in (25,44,1))
  and exists (Select 1 from table C where A.Item_Id = C.Item_ID and subitem_ID in (8,9))
Comments