Mysql with GROUP_CONCAT in subselect

Question

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?

In which select you get first string?What does first string mean? — Mihai
– Mihai, Commented Aug 4, 2014 at 14:03
You should normalize your data (if you can) to avoid such problems. Storing comma separated lists in one column leads most time to trouble. Use a junction table instead. — VMai
– VMai, Commented Aug 4, 2014 at 15:14

Naktibalda · Accepted Answer · 2014-08-04 14:03:30Z

1

Use FIND_IN_SET function to search in comma separated list

WHERE FIND_IN_SET(id, restaurants.sup)

answered Aug 4, 2014 at 14:03

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql with GROUP_CONCAT in subselect

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related