Smartest way to write an "item has properties A,B,C but not X,Y,Z" query

Question

Alright, let's say I have these two tables:

• items with columns id, stuff
• item_properties with columns item_id, prop_id

Now I want to execute a query like

SELECT stuff FROM items WHERE
      EXISTS(SELECT * FROM item_properties WHERE prop_id = 123 AND item_id = items.id)
  AND EXISTS(SELECT * FROM item_properties WHERE prop_id = 456 AND item_id = items.id)
  AND NOT EXISTS(SELECT * FROM item_properties WHERE prop_id = 789 AND item_id = items.id)
  AND NOT EXISTS(SELECT * FROM item_properties WHERE prop_id = 101 AND item_id = items.id)

Which works, but looks ugly and is slow. Can anyone think of a smarter way to do this? I can also get the 123,456 and 789,101 lists via a subquery from a third table, if necessary. I am open to suggestions to change my table design as well.

The number of property IDs I need to check the properties of an item against can vary.

Thanks!

Answer 1

SELECT stuff 
FROM Items as i
INNER JOIN item_properties as ip ON i.id = ip.item_id
WHERE ip.prop_id IN (123, 456) AND ip.prop_id NOT IN (789, 101)

It's NOT TESTED, but that the kind of thing I tend to do a lot.

If I understood your problem correctly, it should work.

Answer 2

SELECT 
  i.*
FROM 
  Items as i
  INNER JOIN item_properties as ip ON i.id = ip.item_id
WHERE 
   ip.prop_id IN (123, 456) 
   AND ip.prop_id NOT IN (789, 101)

Answer 3

if you have a table that contains prop_id to include and to exclude itemsIU (item_id, prop_id, include)

select distinct stuff 
  from items i
  join item_properties ip on i.id = ip.item_id
  join itemsIU iiu on ip.prop_id = iiu.prop_id
group by i.id
having sum(include) = (select count(1) 
                         from itemsIU iiu2 
                        where i.id = iiu2.item_id
                          and iiu2.include = 1)

for you particular example you can use:

select distinct stuff 
  from items i
  join item_properties ip on i.id = ip.item_id
  join (          select 123 prop_id, 1 include
        union all select 456, 1
        union all select 789, 0
        union all select 101, 0) iiu on ip.prop_id = iiu.prop_id
group by i.id
having sum(include) = 2

Answer 4

congratulations, you've stumbled across one of the really good reasons to not use EAV. :)

the best you can do is this:

SELECT *
  FROM items
 WHERE id IN (SELECT id FROM item_properties WHERE prop_id in (123, 456))
   AND id NOT IN (SELECT id FROM item_properties WHERE prop_id in (789, 101))

when mysql gets subquery materialization, some optimizations will occur (such as converting one of the subqueries to a JOIN) but for right now, it will just generate derived tables, which will not scale.