how to use select query in a Group_concat sub query in mysql

Question

I have 3 tables:

tbl_user stores all user details (user_id,name,address)

user_id    name        address
1         a (shop)      home
2         b (shop)    bakerstreet
3         c (staff)   wallstreet
4         d (staff)    georgia
5         e (staff)    sydney

tbl_user_group stores user type (user_id,user_type : 1=shop_owner,2=staff)

user_id    user_type
1             1
2             1
3             2
4             2
5             2

tbl_user_association holds the shop_owner and staff relation (shop_owner_id, staff_id)

shop_owner_id    staff_id
1                3
1                4
1                5
2                3
2                4

desired result

i want to display the list of staffs and the respective shops that they are associated with as follows:

user_id    staff_name    shop_owner
3          c             a,b  
4          d             a,b
5          e             a

I tried using the group_concat as mentioned here. The query is as follows:

SELECT
u.id                          AS user_id,
u.name                        AS staff_name,
(SELECT GROUP_CONCAT(u.name separator ',') FROM tbl_user u WHERE u.id = ua.shop_owner_id) AS 
shop_owner
FROM tbl_user u
JOIN tbl_user_group ug ON u.id = ug.user_id
LEFT JOIN tbl_user_association ua ON u.id = ua.staff_id
WHERE ug.user_type = 2
GROUP BY u.id

But it returns single row of staffs as below. where have i gone wrong?

user_id    staff_name  shop_owner
3              c        a
4              d        a
5              e        a

Replace (SELECT GROUP_CONCAT(u.name separator ',') FROM tbl_user u WHERE u.id = ua.shop_owner_id) AS shop_owner with (GROUP_CONCAT(u.name separator '')) AS shop_owner — Rehban Khatri
– Rehban Khatri, Commented Apr 15, 2017 at 8:42

tiomno · Accepted Answer · 2017-04-15 08:46:55Z

3

This is how I'd do it:

SELECT
    u.user_id,
    u.name,
    GROUP_CONCAT( so.name )
FROM
    tbl_user_group ug
    INNER JOIN tbl_user u
        ON ( ug.user_id = u.user_id )
    INNER JOIN tbl_user_association ua
        ON ( ua.staff_id = u.user_id )
    INNER JOIN tbl_user so -- here join again the user table to get shop owners names
        ON ( ua.shop_owner_id = so.user_id )
WHERE
    ug.user_type = 2
GROUP BY
    u.user_id;

answered Apr 15, 2017 at 8:46

tiomno

2,25730 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

how to use select query in a Group_concat sub query in mysql

desired result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

desired result

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related