Difficult query to select a product which has x attributes...

[daroepi]

Hi, maybe someone can help me or point me in the right direction with the following:

Product {id, name}
100 ipod
101 vcr
102 walkman
103 jukebox

Prod_Attrib {productid, attribtype}
100 music
100 portable
101 video
102 music
102 portable
102 cassette
103 music
103 vinyl

How can I select all products which have both the attributes 'music' and 'portable'? The query must be scalable for multiple attributes (not just the two for this example)...

I've got some ideas using subqueries with IN, and counting the result, but it's a bit of a hack.

Thanks in advance,

Abe

[buntine]

Yes, I would also use s Sub-Query in conjunction with the IN clause.

Do you know the unique identifiers of the Prod_Attrib fields, or will you be using string literals in the query? I will assume you are using string literals (but don't like the idea if they are not forced to be unique!)

SELECT * FROM Product WHERE id IN (SELECT productid FROM Prod_Attrib WHERE attribtype IN ('music', 'portable'));

Depending on your database, you could enhance the query a little bit by forcing the string comparison to be in lower-case, etc. Obviously, this is scalable in that you simply need to add more values to the second IN clause. Hopefully you will unique data and you can modify it to this:

WHERE Prod_Attrib_id IN (1, 2, 3));

Cheers,
Andrew.

[daroepi]

Thanks for the reply.

I had a similar solution in mind, but the problem with the query


SELECT * FROM Product WHERE id IN (SELECT productid FROM Prod_Attrib WHERE attribtype IN ('music', 'portable'));


is that it will also select products that only have one of the attributes (e.g. the jukebox has attribute 'music' but not 'portable'), and I need it to select the products which have all the listed attributes.

I can overcome this problem by counting the number of matching attributes:

SELECT *
FROM Product, (SELECT productid, count(*) as cnt FROM Prod_Attrib WHERE attribtype IN ('music', 'portable') group by productid) as k
WHERE k.cnt=2 AND Product.id=k.productid;


I just don't like the count in the query and I hope there's a better way.

[mattyblah]

If you're using SQL 2005 you might be able to do this with a PIVOT query, but I have no idea on how to use that.