Geek Answers Handbook

MySQL: select multiple rows containing values ​​from one column

I would like to find car_id cars that have "FORD" and "SILVER", and the user input value is "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 | +----+--------+----------+-----------+

In this case, you need to return only one car_id: car_id = 2.

How could you create an SQL query for this?

+4
source share
2 answers

You have a property table. If you want to test several properties at once, you need to join the table:

 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'

The presence of the surrogate id present in the property table is doubtful. He seems to be doing nothing; each property is not property. If id not required by any other element, I would get rid of it and make car_id, name primary key (composite primary key).

+5
source

I assume that each car should have variable parameters, otherwise you would not go with such a setting. It would be much easier if each of them had its own column, and each of them had its own column.

Using the table you provided, you must 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")
+1
source

More articles:


All Articles