select rows in a one to many situation

Question

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...

Answer 1

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

Answer 2

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).

Answer 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.

Answer 4

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.

Answer 5

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.