I have a table named Product related with another table named ProductAttribute.
ProductAttribute:
ID,
ProductId,
Value,
AttributeID
And I need to get all products with these conditions:
AttributeID=1017 AND Value='false'
AttributeID=1011 AND Value='200'
I tried this query
SELECT T0.*
FROM Product T0
INNER JOIN ProductAttribute T1
ON T1.ProductID = T0.ID
WHERE (T1.AttributeID = 1011 AND T1.Value = 'false')
AND
(T1.AttributeID = 1017 AND T1.Value = '200')
But the result was empty. And tried this query
SELECT T0.*
FROM Product T0
INNER JOIN ProductAttribute T1
ON T1.ProductID = T0.ID
WHERE (T1.AttributeID = 1011 AND T1.Value = 'false')
OR
(T1.AttributeID = 1017 AND T1.Value = '200')
But the result was every row has one of the two conditions.
I need the rows that fulfill the two conditions together.
this is the ProductAttribute table's data:
CodePudding user response:
Do a GROUP BY
in a derived table (the subquery) to make sure both attributes are there. Then JOIN
.
SELECT T0.*
FROM Product T0
INNER JOIN
(
select ProductID
from ProductAttribute
WHERE (AttributeID = 1011 AND Value = 'false')
OR (AttributeID = 1017 AND Value = '200')
group by ProductID
having count(distinct AttributeID) = 2
) T1 ON T1.ProductID = T0.ID
(I'd rather chose table aliases that make sense, like p
for Product and pa
for ProductAttribute...)