4 tables SQL connection error

Question

I have four tables, for example:

Cars: car_id | make_id 1 | 1 2 | 3 Cars Makes make_id | make_name 1 | BMW 2 | Ferrari 3 | Mercedes Car Properties car_id | property_id | property_value 1 | 1 | Automatic 1 | 2 | 1000 1 | 3 | Diesel 2 | 1 | Manual 2 | 2 | 15000 2 | 3 | Gasoline Properties property_id | property_name 1 | Transmission 2 | Mileage 3 | Fuel 

As you can see, each car has a make_id from the “Does” table.

And there is a separate table that contains all the basic properties of cars. Then there is the table "Car Properties", which contains car_id, property_id, property_value

Now I want to make the following request: get BMW cars with an automatic transmission, and mileage - 1000 with diesel. let the form provide the following: make_id = 1 (BMW) properties = Automatic, 1000, Diesel

PS: It will be ok if I just get the result car_id

Answer 1

Assuming that the request should satisfy all three properties:

 SELECT c.car_id FROM Cars c INNER JOIN ( SELECT car_id, COUNT(*) AS prop_count FROM CarProperties WHERE (property_id = 1 AND property_value = 'Automatic') OR (property_id = 2 AND property_value = '1000') OR (property_id = 3 AND property_value = 'Diesel') GROUP BY car_id ) AS cp ON c.car_id = cp.car_id AND cp.prop_count = 3 WHERE c.make_id = 1; 

Then it occurred to me:

 SELECT c.car_id FROM Cars c INNER JOIN ( SELECT car_id FROM CarProperties WHERE property_id = 1 AND property_value = 'Automatic' ) AS t ON c.car_id = t.car_id INNER JOIN ( SELECT car_id FROM CarProperties WHERE property_id = 2 AND property_value = '1000' ) AS m ON c.car_id = m.car_id INNER JOIN ( SELECT car_id FROM CarProperties WHERE property_id = 3 AND property_value = 'Diesel' ) AS f ON c.car_id = f.car_id WHERE c.make_id = 1; 

Answer 2

Not 100% of the mysql syntax (sorry too much in TSQL), but it is an interconnection idea.

 FROM Car JOIN CarProperties Trans ON Car.car_id = Trans.CarID AND Trans.property_id = 1 JOIN CarProperties Mileage ON Car.car_id = Mileage.CarID AND Mileage.property_id = 2 JOIN CarProperties Fuel ON Car.car_id = Fuel.CarID AND Fuel.property_id = 3 

Your choice can be drawn from mileage, fuel, or trance, just like your where clause

Answer 3

Since the input only matters, such as “Automatic, 1000, Diesel” and not a single “Transmission, Mileage, Fuel”, you will have to ignore the property table and pray that your property types never include overlapping keys (or more than one numeric a type). Also, since the input already has make_id, we can also leave the Cars Makes table.

Another trick here is that you can join the same table multiple times.

 SELECT c.car_id FROM cars c INNER JOIN `Car Properties` cp1 ON cp1.car_id = c.car_id AND cp1.property_value = 'Automatic' INNER JOIN `Car Properties` cp2 ON cp2.car_id = c.car_id AND cp2.property_value = 'Diesel' INNER JOIN `Car Properties` cp3 ON cp3.car_id = c.car_id AND cp3.property_value = '1000' 

Answer 4

you can first join the properties and properties of the car, give it an alias and get the car_id array, then search the cars. "Cars" does "IN" (car_id_array).

Answer 5

Perhaps this might work:

 SELECT car_id FROM Cars LEFT JOIN CarMakes USING (make_id) JOIN (SELECT car_id FROM CarProperties JOIN Properties USING (property_id) WHERE property_name='Transmission' AND property_value='Automatic') a JOIN (SELECT car_id FROM CarProperties JOIN Properties USING (property_id) WHERE property_name='Mileage' AND property_value='1000') b JOIN (SELECT car_id FROM CarProperties JOIN Properties USING (property_id) WHERE property_name='Fuel' AND property_value='Diesel') c WHERE make_name = 'BMW' 

Answer 6

 select cars.car_id from cars join `car properties` cp on cars.car_id = cp.car_id join properties p on cp.property_id = p.property_id where cars.make_id = 1 and ( ( p.property_name = "Transmission" and cp.property_Value = "Automatic" ) OR ( p.property_name = "Mileage" and cp.property_Value = "1000" ) OR ( p.property_name = "Fuel" and cp.property_Value = "Diesel" ) ) group by cars.car_id having count(*) = 3