Many to many search query

Databases
1

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


	SELECT
		 c.id
		,c.model
		,c.year
		,c.price
		,c.desc
		,c.power
		,b.name AS brand
		,f.name AS fuel
		,t.name AS type
	FROM
		Cars c
		LEFT JOIN
		Brands b
			ON c.brend_id = b.id
		LEFT JOIN
		Fuels f
			ON c.fuel_id = f.id
		LEFT JOIN
		Types t
			ON c.type_id = t.id
	WHERE
		b.name = 'ford'
		AND
		2 = (	SELECT
				COUNT(*)
			FROM
				CarsToEquipment cte
				LEFT JOIN
				Equipment e
					ON cte.equipment_id = e.id
			WHERE
				cte.car_id = c.id
				AND
				e.name IN ('abs', 'esp')
		)
2

all your joins should be INNER

otherwise that looks fine

3

Thank you.

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?

4

you are right, not mandatory means the foreign key can be null, so those would be okay as LEFT OUTER JOINs

:slight_smile: