Let's say I have a table Cars with a id, brand, model etc., and table Equipment with id, name, description.
The two tables are connected by a junction table (many to many relationship).
I want to run a query: "find all cars with brand named Ford and equipment named ABS and ESP", so I'll find all Fords equiped with at least ABS and ESP.
Cars (id, brand_id, model...)
CarsToEquipment (car_id, equipment_id)
Equipment (id, name, description)
Brands (id, name...)
What is most efficient query for this?
At the moment I use something like this (I shortened it), but I suppose it could be done in a better way (or improved):
,b.name AS brand
,f.name AS fuel
,t.name AS type
ON c.brend_id = b.id
ON c.fuel_id = f.id
ON c.type_id = t.id
b.name = 'ford'
2 = ( SELECT
ON cte.equipment_id = e.id
cte.car_id = c.id
e.name IN ('abs', 'esp')