Sql query for table with product id and several values for that id

Question

I apologize for the title but I didnt't know how to explain better in a few words my problem. I have a table with car id, property id and value of the property. Something like that:

 CarID | propertyID | propertyValue
    1        1            black
    1        2            diesel
    1        3            automatic gear
    1        4            80 kW
    2        1            blue
    2        2            gasoline
    2        3            manual gear
    2        4            120 kw
    3        1            blue
    3        2            gasoline
    3        3            manual gear
    3        4            90 kw

I can't get a valid query to get a car with property value for example black and automatic gear and diesel. I tried with intersect and union but without result.

select distinct t.*
from table t
where value ilike 'blue' 
intersect select t.*
from table t
where value ilike 'manual gear' 
union  select t.*
from table t
where value ilike '90 kw'

Please help. Thanks.

Edit: I must apologize again but I forgot to add more info. I added the case when all properties are the same except power and I want to get the car with 90 kw. The database is PostgreSQL. Thank you in advance.

Answer 1

One canonical way of achieving what you want is to aggregate by CarID, retaining only records having the properties you want, then check to make sure that the distinct count of properties matches all the ones in your desired list.

WITH cte AS (
    SELECT CarID
    FROM table
    WHERE propertyValue IN ('black', 'automatic gear', 'diesel')
    GROUP BY CarID
    HAVING COUNT(DISTINCT propertyValue) = 3
)
SELECT * FROM cte

If you want to instead return all of a matching car's records, and all its columns, then you can use the CTE as follows:

SELECT *
FROM table t1
INNER JOIN cte t2
    ON t1.CarID = t2.CarID

Demo here:

Rextester