CODEWITHSUNDEEP
mysqlsqlselectgroup-concatsubquery
Cooler
Cooler

Reputation: 55

Mysql with GROUP_CONCAT in subselect

I have problem with subselect in mysql. In table restaurants I have field "sup" where I have IDs separated by comma. Basic select: 

mysql> select name, sup from restaurants LIMIT 5;
+-------------------------------------+---------+
| name                                | sup     |
+-------------------------------------+---------+
| Pizzerija in špagetarija Buf        | 2,14,18 |
| EJGA - KAVARNA - RESTAVRACIJA - PUB | 11,17   |
| Restavracija Center                 | 5,22    |
| Restavracija Viola                  | 5,13,17 |
| Gostilna Anderlič                   | 5,17    |
+-------------------------------------+---------+
5 rows in set (0.00 sec)

I want to know the field "SI" from table suply for IDs in sup.restaurants table. So my select for that is: 

mysql> SELECT GROUP_CONCAT(suply.SI SEPARATOR ', ')  FROM `suply` WHERE id IN (2,14,18);
+---------------------------------------+
| GROUP_CONCAT(suply.SI SEPARATOR ', ') |
+---------------------------------------+
| Italijanska, Špagetarija, Picerija    |
+---------------------------------------+
1 row in set (0.00 sec)

So I wrote select with subselct but doesn't work well: 

mysql> SELECT restaurants.name,
    -> (SELECT GROUP_CONCAT(suply.SI SEPARATOR ', ')  FROM `suply` WHERE id IN (restaurants.sup)) AS hrana
    ->  FROM restaurants
    ->  LIMIT 5;
+-------------------------------------+--------------------+
| name                                | hrana              |
+-------------------------------------+--------------------+
| Pizzerija in špagetarija Buf        | Italijanska        |
| EJGA - KAVARNA - RESTAVRACIJA - PUB | Mednarodna kuhinja |
| Restavracija Center                 | Slovenska domača   |
| Restavracija Viola                  | Slovenska domača   |
| Gostilna Anderli?                   | Slovenska domača   |
+-------------------------------------+--------------------+
5 rows in set (0.00 sec)

Why in this select I get just first string?

Upvotes: 1

Views: 364

Answers (1)

Naktibalda
Naktibalda

Reputation: 14102

Use FIND_IN_SET function to search in comma separated list

WHERE FIND_IN_SET(id, restaurants.sup)

Upvotes: 1

Related Questions