MySQL: Select multiple rows containing values from one column

Question

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?

@OMG: wha indeed. It must be late, disregard anything I say... — Wrikken
– Wrikken, Commented Sep 29, 2010 at 1:12

bobince · Accepted Answer · 2010-09-29 01:12:28Z

5

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

answered Sep 29, 2010 at 1:12

bobince

538k111 gold badges675 silver badges846 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ruben Over a year ago

However, the 200 can be anywhere. not necessarily in the TOPSPEED.. (its a user input search string)

Ruben Over a year ago

Sorry, guess removing AND c2.name='TOPSPEED' and replacing ='200KM/H' with LIKE '%200%' does the trick already..

stevendesu · Accepted Answer · 2010-09-29 00:39:51Z

2

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

answered Sep 29, 2010 at 0:39

stevendesu

17k23 gold badges120 silver badges209 bronze badges

Collectives™ on Stack Overflow

MySQL: Select multiple rows containing values from one column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related