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

The biggest reason you are having difficulties here is you have set up your table poorly and are not taking advantage of relational databases' full potential. Instead of a table that has fields

CarID | propertyID | propertyValue

You should have

CarID | Color | FuelType | TransmissionType

Then your query is much simpler:

SELECT * FROM tablename 
WHERE  Color LIKE 'black' 
AND FuelType LIKE 'diesel' 
AND TransmissionType LIKE 'auto'

I know it is an answer that departs from your existing setup, but if you modify your table as recommended you will make your life considerably easier.

EDIT

I came up with a different approach for you based on your existing table structure. Use a WITH statement to reform your table into a better designed one by selecting your primary key, then using subqueries to join on the same table for each individual property value. Then, select from that:

WITH newtable AS (
SELECT ot.CarID, p1.propertyValue AS CarColor, p2.propertyValue AS FuelType, 
    p3.propertyValue AS TransType, p4.propertyValue AS EnginePower
FROM oldtable ot
LEFT JOIN (SELECT CarID, propertyValue FROM oldtable WHERE propertyID = 1) p1
    ON p1.CarID = ot.CarID 
LEFT JOIN (SELECT CarID, propertyValue FROM oldtable WHERE propertyID = 2) p2
    ON p2.CarID = ot.CarID
LEFT JOIN (SELECT CarID, propertyValue FROM oldtable WHERE propertyID = 3) p3
    ON p3.CarID = ot.CarID
LEFT JOIN (SELECT CarID, propertyValue FROM oldtable WHERE propertyID = 4) p4
    ON p4.CarID = ot.CarID)
SELECT * FROM newtable
WHERE CarColor LIKE 'blue' AND TransType LIKE 'manual' AND EnginePower LIKE '90 KW'
ORDER BY CarID

Answer 2

valid query to get a car with property value for example black and automatic gear and diesel

If we want cars that have color = black, transmission = automatic gear & fuel type = diesel, where, inferring from the sample data color, transmission & fuel type have property IDs 1, 2 & 3 respectively

Then we could employ json aggregation on the cars' properties & check that our filter conditions are a subset to the car's properties.

SELECT 
  "CarID"
, JSONB_OBJECT_AGG("propertyID", "propertyValue") properties
FROM cars
GROUP BY 1
HAVING JSONB_OBJECT_AGG("propertyID", "propertyValue") @> '{"1":"black"}'::JSONB

This is valid in posgresql 9.4+. It returns the CarID that matches the key & value filters specified in the HAVING clause. It is possible to discard the properties column if you only need the CarID

We can also achieve the same using Array Aggregation & a HAVING comparison like this. Arrays have been in postgres for a very long time & the contains operator @> has also been in postgres for a very long time. This should work on all recent postgresql versions.

SELECT 
  "CarID"
, ARRAY_AGG(("propertyID"::INT, "propertyValue"::TEXT)) properties
FROM cars
GROUP BY 1
HAVING ARRAY_AGG(("propertyID", "propertyValue")) @> ARRAY[(1::INT,'black'::TEXT),(2,'diesel'::TEXT),(3,'automatic gear'::TEXT)];

Answer 3

For completeness, you could indeed take your result with an intersect query as follows:

select t.CarID
from t
where propertyID = 1 and propertyValue = 'blue' 
intersect 
select t.CarID
from t
where propertyID = 3 and propertyValue = 'manual gear' 
intersect  
select t.CarID
from t
where propertyID = 4 and propertyValue = '90 kW'

Your own query's primary problem is that your SQL reads from table t instead of from t. Moreover you introduce a union instead of continuously using intersect.

Finally, I added propertyID to my query, please read the comments under Tim's answer if you don't understand why.

Having said that, I still prefer Tim's answer for your problem setup and support almost fully the design objections raised by SandPiper.

Answer 4

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

Answer 5

One way

select * from (
    select your_table.*, count(*) over(partition by CarID) property_cnt 
    from your_table where propertyValue in ('black', 'diesel', 'automatic gear' )
) t where property_cnt = 3