MySQL: Select multiple rows containing values from one column

Question

Dear all,

I'd like to find the car_id's of the cars that have 'FORD' AND 'SILVER' AND the user input value of '200' in the value column:

table_cars

    +----+--------+----------+-----------+
    | id | car_id | name     | value     |
    +----+--------+----------+-----------+
    | 1  | 1      | MAKE     | FORD      |
    | 2  | 1      | CARLINE  | FIESTA    |
    | 3  | 1      | COLOR    | SILVER    |
    | 4  | 1      | TOPSPEED | 210KM/H   |
    | 5  | 2      | MAKE     | FORD      |
    | 6  | 2      | CARLINE  | FOCUS     |
    | 7  | 2      | COLOR    | SILVER    |
    | 8  | 2      | TOPSPEED | 200KM/H   |
    | 9  | 3      | MAKE     | HOLDEN    |
    | 10 | 3      | CARLINE  | ASTRA     |
    | 11 | 3      | COLOR    | WHITE     |
    | 12 | 3      | TOPSPEED | 212KM/H   |
    +----+--------+----------+-----------+

Which in this case should return only one car_id: car_id = 2.

What would be the way to go to create the SQL query for this?

Answer 1

I assume that every car needs to have variable parameters, otherwise you wouldn't have gone with a setup like this. It would be much easier if MAKE, CARLINE, COLOR, and TOPSPEED each had their own column.

Using the table you've provided, however, you need to use subqueries. http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

The query should look something like this (untested):

SELECT * FROM table_cars WHERE id IN (SELECT * FROM table_cars WHERE name="MAKE" AND value="FORD") AND id IN (SELECT * FROM table_cars WHERE name="COLOR" AND value="SILVER") AND id IN (SELECT * FROM table_cars WHERE name="TOPSPEED" AND value="200KM/H")

Answer 2

What you have is a properties table. When you want to test multiple properties at once you need to join the table to itself:

SELECT c0.car_id
FROM table_cars AS c0
JOIN table_cars AS c1 ON c1.car_id=c0.car_id
JOIN table_cars AS c2 ON c2.car_id=c1.car_id
WHERE c0.name='MAKE' AND c0.value='FORD'
AND c1.name='COLOR' AND c1.value='SILVER'
AND c2.name='TOPSPEED' AND c2.value='200KM/H'

Having the surrogate id present in a properties table is questionable. It doesn't seem to be doing anything; each property isn't an entity of its own. Unless the id is required by some other element, I'd get rid of it and make car_id, name the primary key (a composite primary key).