How to combine multiple queries in MySQL?

Question

Backgronud

I have a DB of restaurants and restaurant chains. In my DB, each restaurant chain has a series of specific restaurants.

For example, the PizzaHut's restaurant chain has restaurants in my zone.

Objective

I want to know, given an array of cityIds, how many restaurant chains are there. For example, given the array [1, 2] (London and Lisbon), I want to know the ids of the restaurant chains each city has:

[
    {cityId: 1, chains: [1, 2, 3]}, 
    {cityId: 2, chains: [4, 5]}
]

Problem

The problem is that I don't know how to do that in a single query. Thus far, I manager to get all the chain ids for a single city:

SELECT DISTINCT id
FROM   restaurant_chains
       INNER JOIN restaurants
               ON restaurant_chains.id = restaurants.chainId
       WHERE  restaurants.city = MY_CITY_ID_HERE

Here I do an INNER JOIN between my restaurant_chains table and my restaurantstable ON the chain's Id.

Then I go through each restaurant and I check if it is in the city I want.

Research

The only way to do this for an array of cityIds would be to have an array. But MySQL doesn't have arrays. So I searched and I found WHILE loops, UNION ALL and TMP_TABLES:

How can I simulate an array variable in MySQL?

All of which I honestly don't understand very well and I don't how to apply to my example.

QUESTION

How to do that single query for an array of cities?

try using a varaible in mysql. https://dev.mysql.com/doc/refman/5.7/en/user-variables.html — chirag satapara, Oct 09 '17 at 08:00

score 1 · Accepted Answer · answered Oct 09 '17 at 07:58

Just select the city id in addition to everything else you wanted. With that, you can walk through the results and associate each with the city id.

SELECT DISTINCT restaurants.city, id
FROM   restaurant_chains
       INNER JOIN restaurants
               ON restaurant_chains.id = restaurants.chainId
       WHERE  restaurants.city IN (CITY_ID1, CITY_ID2, CITY_ID3...)