1

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
Community
  • 1
  • 1
Avshek
  • 25
  • 8

1 Answers1

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;
tiomno
  • 2,178
  • 26
  • 31