Wednesday, March 21, 2012

Help with relational query - many to many

Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused.
I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item.
A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID IN (10, 13, 17)

But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull outONLYitems that have a AttributeID of 10 AND 13 AND 17.
Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :S
I've also tried:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID = 10 AND AttributeID = 13 etc
But obviously that won't work! :sSelect *
from items i
where exists (select null from linking where link_itemID = i.item_id and link_attributeID = 10) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 13) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 17)

Nick|||Sorry for the late reply! Thanks for your post nick!
i got it working :)

No comments:

Post a Comment