MySQL Join Query (maybe two internal joins)

Question

I currently have the following:

Table:

• ID
• name
• region

Table Provider:

• ID
• name
• town_id

The following query returns the number of suppliers for each city:

SELECT t.id, t.name, count(s.id) as NumSupplier
FROM Town t 
INNER JOIN Suppliers s ON s.town_id = t.id 
GROUP BY t.id, t.name

Now I want to present another table in a query, Supplier_vehicles. A supplier can have many vehicles:

Provider_vehicles table:

• ID
• supplier_id
• vehicle_id

Now, the NumSupplier field should indicate the number of suppliers for each city that have any of the car_id data (state IN):

The following query will simply return vendors that have any of the vehicle_id data:

SELECT * FROM Supplier s, Supplier_vehicles v WHERE s.id = v.supplier_id AND v.vehicle_id IN (1, 4, 6)

I need to integrate this into the first request so that it returns the number of providers that have any of the vehicle_id data.

Answer 1

SELECT t.id, t.name, count(s.id) as NumSupplier
FROM Town t 
INNER JOIN Suppliers s ON s.town_id = t.id 
WHERE s.id IN (SELECT sv.supplier_id
               FROM supplier_vehicles sv 
               WHERE sv.vehicle_id IN (1,4,6))
GROUP BY t.id, t.name

INNER JOIN ( INNER, ) COUNT(s.id) TO COUNT(DISTINCT s.id)

Answer 2

, LEFT OUTER JOIN.

, , -

... LEFT OUTER JOIN (SELECT * FROM Supplier s, Supplier_vehicles......) s ON s.town_id = t.id

, "" . , .

Answer 3

SELECT t.name, count(s.id) as NumSupplier
FROM Town t
LEFT OUTER JOIN Suppliers s ON t.id = s.town_id
LEFT OUTER JOIN Supplier_vehicles v ON s.id = v.supplier_id
WHERE v.vehicle_id IN (1,4,6)
GROUP BY t.name