Filter table rows with multiple conditions from related table

March 2, 2022

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:

>Solution :

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