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')
all your joins should be INNER
otherwise that looks fine
But, about joins... Definitely I should use INNER JOIN for Brands and inside subquery (CarsToEquipment cte INNER JOIN Equipment e), but if I use all INNER JOIN, Car with unknown Fuel or Type etc. (not mandatory fields) won't be shown?
you are right, not mandatory means the foreign key can be null, so those would be okay as LEFT OUTER JOINs