Multiple WHERE conditions on the same table columns

Question

I am trying to get parts which have certain attributes. Each part has many attributes, and each attribute has an attributeheader. So there's three tables that I need to use.

SELECT * FROM Part p 
LEFT JOIN p.attributes pa
JOIN pa.attributeheader pah
WHERE (sph.name = 'size' AND sp.value IN ('11')) 
AND (sph.name = 'colour' AND sp.value IN ('Black'))

This returns nothing as I've gone wrong somewhere on the JOIN, or the lack of grouping. I'm trying to select the parts which have both attributes, so using OR is not a solution. I can't quite work out how to go about this - using raw MySQL I would use aliases on the join.

Answer 1

WHERE (sph.name = 'size' AND sp.value IN ('11'))
AND (sph.name = 'colour' AND sp.value IN ('Black'))

is equivalent to

WHERE sph.name = 'size' AND sp.value IN ('11') 
AND sph.name = 'colour' AND sp.value IN ('Black')

and of course sph.name cannot be equal to both 'size' and 'colour' at the same time, therefore you won't ever get any row from this SELECT.

You need to join attributes table twice.

More or less this way:

SELECT * FROM (Part p 
LEFT JOIN p.attributes pa
JOIN pa.attributeheader pah)
LEFT JOIN p.attributes pa2
JOIN pa.attributeheader pah2

And you will need to prefix values in the WHERE clause with pa. or pa2. and so on.

And maybe you shouldn't execute such queries in a relational database but rather in a datawarehouse or an indexed search engine supporting faceting.