select rows in a one to many situation

Hi everybody!
I think that I am stuck with this particular situation:

Here are my tables:

item_table:
id | item
1 : A
2 : B
3 : C

attr_table:
attr | item_id
1 : 1
1 : 2
2 : 1
2 : 3
3 : 2
3 : 3
I would like to know if it is technically possible to retrieve any item which is associated with attr = 1 and 3. The answer should be 'B' only. Likewise, if I request an item which is associated with attr = 1 and 2, I should get 'A' only.

The thing is that attr_table can potentially have a lot of rows and I wish to make only one query.

This question sounds easy and I am quite upset for not being able to answer it.

I was hoping that someone smarter could give me a hand...

From stackoverflow

• The example is written for SQLServer but the query should work in mysql as wel.

  Key is the HAVING COUNT statement being equal to the amount of attributes that have to match. If the attributes should be (1, 2, 5), you'll have to change the count to 3.

  DECLARE @item_table TABLE (ID INTEGER PRIMARY KEY, Item CHAR(1))
  DECLARE @attr_table TABLE (Attr INTEGER, Item_ID INTEGER)
  
  INSERT INTO @item_table VALUES (1, 'A')
  INSERT INTO @item_table VALUES (2, 'B')
  INSERT INTO @item_table VALUES (3, 'C')
  
  INSERT INTO @attr_table VALUES (1, 1)
  INSERT INTO @attr_table VALUES (1, 2)
  INSERT INTO @attr_table VALUES (2, 1)
  INSERT INTO @attr_table VALUES (2, 3)
  INSERT INTO @attr_table VALUES (3, 2)
  INSERT INTO @attr_table VALUES (3, 3)
  
  
  SELECT Item
  FROM @item_table i
       INNER JOIN @attr_table a ON a.Item_ID = i.ID
  WHERE a.Attr IN (1, 3)
  GROUP BY Item
  HAVING COUNT(a.Attr) = 2
  

  Blank Xavier : Isn't doing an inner join is properly tantamount to a second select?

  tpdi : This is fragile, using the `count =`, as it won't work unless there are exactly two attr rows corresponding to the item with the specified attrs. But worse, it fails if there are two attr rows with item_id = 1 and attr = 1 -- so it brings back a false positive, an item with no attr = 3. And it adds the cost of a group by.

  Lieven : @Blank Xavier, I'm sorry but I don't understand what you are trying to get at.

  Lieven : @tpdi, I assume there is a unique constraint on (Attr, Item_ID) in the attr_table.

• select distinct item_table.item from item_table, attr_table
  where item_table.id = attr_table.item_id
  and attr_table.attr = 1 and attr_table.attr = 3;
  

  Basically it does the matching you'd expect and ends up with a ton of rows - but then the distinct keyword operates, so you get the minimal unique set of rows as your final result.

  (Interally, I'd hope it's more efficient, but not bothering to make up the full list of matching rows).

  James Anderson : Does not actually work as there is no single row where "attr = 1 and attr = 2" is true.

  Blank Xavier : I don't understand. Is the '2' a typo? should it be '3'?

•    SELECT * From attr_table a, item_table i
     where a.item_id = i.id
     and a.attr = 1
     and a.item_id  in (select item_id from attr_table where  attr = 3);
  

  Does the job returning one row for item B.

• select * from item_table a 
  where exists ( select * from attr_table b 
                 where b.item_id = a.id and b.attr = 1)
  and exists ( select * from attr_table c 
               where c.item_id = a.id and c.attr = 3);
  

  Note that this query says exactly what your specification says: get me all the rows from item_table where there exists at least one row from attr_table that has that row's id and the first attr specified and where there exists at least one row from attr_table that has that row's id and the second attr specified.

• This is probably way too late, but I would suggest using a couple of joins like so:

  select i.item, b.item_id, c.item_id 
  from item_table i 
  join attr_table b on i.id=b.item_id and b.item_id=1
  join attr_table c on i.id=c.item_id and c.item_id=2
  

  That's how I do it.