MySQL Joining ON column a IN (column b)

Hi I'm wondering if it is possible to do something like below. Obviously I've tried to run this in phpMyAdmin but there is an error. Perhaps there is another way to write this query.

SELECT * 开发者_开发问答FROM eat_eat_restaurants AS r 
INNER JOIN eat_eat_cuisines AS c ON c.cuisine_id IN (r.cuisine_ids)

Further to this is it possible to select the restaurant name and cuisines in one row? e.g.

r_name    c_names
Marco's   Italian, Modern European

My 2 tables look like so:

TABLE EAT_EAT_RESTAURANTS
id  r_name    cuisine_ids
1   Marco's   1,2
2   Beldaro   3,4
3   Benny's   1,3


TABLE EAT_EAT_CUISINES
id  c_name
1   Italian
2   Modern European
3   Greek
4   Belgian
5   ...

Your current schema is not normalized and very inefficient. Try 3 tables: restaurants, cuisines and restaurant_cuisines and do the query like this:

SELECT r.r_name, GROUP_CONCAT(c.c_name)
FROM restaurants r JOIN restaurant_cuisines rc ON (r.id=rc.r_id)
 JOIN cuisines c ON (rc.c_id=c.id)
GROUP BY r.r_name;

No, the syntax doesn't work like that.

What you need to do is create a cross-table between the Restaurants and Cuisines, since a Cuisine can belong to multiple Restaurants and a Restaurant offers multiple Cuisines. After that, you can JOIN the three tables together to get the name of the Restaurant as well as all the names of the Cuisines they offer.

The cross table would have fields like this:

id   restaurant_id   cuisine_id
1    1               1
2    1               2
3    2               1
4    2               3
5    4               1

need third table:

TABLE EAT_EAT_CUISINES_LOOKUP
r_id    cuisine_ids 1   
1       1
1       2 
// for Marco

next JOIN to it, if results in 1 line needed use GROUP_CONCAT()

As an other solution I would recommend to use a join table which makes your query simple. Let's name it: restaurant_cuisine An example:

Id restaurant_id cuisine_id
1        1          1
1        1          2