SQL query to find the most number of vendors per a given location

Question

Can you help me to find the proper MySQL query to get the most numbers of vendors per a given location and list them all by name and name of their shop:

1 - The query must find out which location has the highest number of vendors then list them by name with the name of the shop they work in.

I have the following tables:

CITIES 
(
 ID "unique",
 NAME
)


SHOPS
(
 ID "unique",
 NAME,
 CITY_ID ( foreign key of CITIES TABLE ID)
)

VENDORS
(
 ID "unique",
 NAME,
 SHOP_ID ( foreign key of SHOPS TABLE ID)
)

Example with dummy data

CITIES : NY, SF

SHOPS: Boom - NY, Flash - NY, Sofast - SF

Vendors:

Mark : Boom,
John : Boom,
Carlos : Sofast,
Alex : Sofast,
David : Flash,
James: Flash

The NY has the highest number of vendors so it should list

Mark : Boom, John : Boom, David : Flash, James: Flash

Answer 1

Check if this works -

Select vendors.name, shops.name 
from 
cities inner join shops on cities.id= shops.city_id
inner join vendors on shops.id = vendors.shop_id
where cities.id = (select id from (select cities.id, count(1) from 
cities inner join shops on cities.id= shops.city_id 
inner join vendors on shops.id = vendors.shop_id 
group by cities.id order by 2 desc) limit 1)