Mysql Split String and select with results
I have 2 tables that look like this:
frontend_users:
uid | usergroup | name
1 | 1,2,3 | Michael
2 | 2 | Tobias
3 | 1 | Colin
...
and
usergroups:
uid | title
1 | member
2 | reporter
3 | admin
I tried to split the user group and select a user group for each number and add it to the display result, which should look at best:
uid | name | groups
1 | Michael | member, reporter, admin
2 | Tobias | reporter
3 | Colin | member
The best result I have found so far was from here: Split string and do calculation in MySQL but I can't seem to select the substring_index result from another table. My current request looks like this:
SELECT frontend_users.uid, frontend_users.name
CASE
WHEN frontend_users.usergroup LIKE '%,%,%' THEN
CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
(SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1)),
(SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 3), ',', -1)))
WHEN frontend_users.usergroup LIKE '%,%' THEN
CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
(SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1))),
ELSE (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = frontend_users.usergroup)
END AS groups
FROM frontend_users, usergroups
I tried using a for loop for the number of groups, but the results were even worse.
Any hints how can I use a select query with a substring_index result?
+1
source to share
2 answers
You have to think about normalization. However, for the current scheme, consider the following
mysql> select * from frontend_users ;
+------+-----------+---------+
| uid | usergroup | name |
+------+-----------+---------+
| 1 | 1,2,3 | Michael |
| 2 | 2 | Tobias |
| 3 | 1 | Colin |
+------+-----------+---------+
3 rows in set (0.00 sec)
mysql> select * from usergroup ;
+------+----------+
| uid | title |
+------+----------+
| 1 | member |
| 2 | reporter |
| 3 | admin |
+------+----------+
3 rows in set (0.00 sec)
To get the result you want, you can use the following query which is inefficient for the long term
select
u.uid,
u.name,
group_concat(g.title) as groups
from frontend_users u
join usergroup g on find_in_set(g.uid,u.usergroup) > 0
group by u.uid ;
+------+---------+-----------------------+
| uid | name | groups |
+------+---------+-----------------------+
| 1 | Michael | admin,reporter,member |
| 2 | Tobias | reporter |
| 3 | Colin | member |
+------+---------+-----------------------+
Now the best approach is to create an association table like
mysql> create table user_to_group (uid int, gid int);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into user_to_group values (1,1),(1,2),(1,3),(2,2),(3,1);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
And the best query would be
select
u.uid,
u.name,
group_concat(g.title) as groups
from frontend_users u
join user_to_group ug on ug.uid = u.uid
join usergroup g on g.uid = ug.gid
group by u.uid ;
+------+---------+-----------------------+
| uid | name | groups |
+------+---------+-----------------------+
| 1 | Michael | member,admin,reporter |
| 2 | Tobias | reporter |
| 3 | Colin | member |
+------+---------+-----------------------+
+1
source to share
Abhik Chakraborty's answers are the best solution, but I'm just fixing your last code if you'd like.
you can check it on fiddle
SELECT frontend_users.uid, frontend_users.name,
CASE
WHEN usergroup LIKE '%,%,%' THEN
CONCAT(
(
SELECT title FROM usergroups
WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 1), ',', -1)
)
,',',
(
SELECT title FROM usergroups
WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 2), ',', -1)
)
,',',
(
SELECT title FROM usergroups
WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 3), ',', -1)
)
)
WHEN usergroup LIKE '%,%' THEN
CONCAT(
(
SELECT title FROM usergroups
WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 1), ',', -1)
)
,',',
(
SELECT title FROM usergroups, frontend_users
WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 2), ',', -1) Limit 1
)
)
ELSE
(SELECT title FROM usergroups, frontend_users WHERE usergroups.uid = usergroup Limit 1)
END AS groups
FROM frontend_users
0
source to share