Join tables with many to many relationships and show the result of query on one single row

Question

I have a situation where I have 2 tables 'users' and 'roles' table where they are related to each other by many to many relationship, many to many relationship stored in the 3rd table called 'UserRoles'. I want to write a query to show distinct users but show all the roles associated with a particular user in one single row.

For example, the 3 tables as follows:

users   
UserName    UserId
test11      000_1
test12      000_2
test13      000_3
test14      000_5

roles   
Role Name   Role ID
Admin       100_1
superuser   100_2
reader      100_3
writer      100_4

UserRoles   
RoleID  UserID
100_1   000_1
100_2   000_1
100_3   000_1
100_2   000_2
100_3   000_2
100_4   000_2
100_3   000_3
100_4   000_3
100_1   000_5
100_2   000_5
100_3   000_5
100_4   000_5

so how can I write a query that displays a row as below with roles separated with semicolons for every users?

UserName    Role Name
test11      Admin;superuser;reader

Can you please provide the name of the DB you are using? The right answer will depend on it because, to my knowledge, there isn't a DB-independent solution for doing this in a single query. — Owen Allen, Jun 10 '13 at 07:39
In Postgres you can use `string_agg()` to achieve that. Other DBMS have similar aggregate functions — , Jun 10 '13 at 07:45
You can take a look here http://stackoverflow.com/a/16978585/1920232 — peterm, Jun 10 '13 at 07:50

score 1 · Answer 1 · answered Jun 10 '13 at 07:52

If we are to believe your question exactly and assume you are using MySQL:

select UserName, 
    GROUP_CONCAT(DISTINCT r.`Role Name` SEPARATOR ';') as `Role Name`
  FROM users u JOIN UserRoles ur on u.UserId = ur.UserID 
       JOIN roles r on ur.RoleId = r.`Role ID`

Mez · Answer 2 · 2013-06-10T08:17:59.660

You should be able to work with the following function, assuming you are using MS SQL :


create FUNCTION [dbo].[GetUserRoles] (@UserId varchar(50))RETURNS varchar(max) AS

BEGIN

declare @result  varchar(max)
select @result = ''
 select @result = @result +  r.rolename+';'  from ROLES as r inner join USERROLES as rr on rr.roleid = r.roleid
 and rr.userid=@UserId

 if charindex(';',@result) >0
 begin 
 SELECT @result = SUBSTRING(@result, 1, NULLIF(DATALENGTH(@result)-1,-1)) 
 end

return @result
end

Then call the function like this :

select username, [dbo].[GetUserRoles](userid)
from dbo.users

Join tables with many to many relationships and show the result of query on one single row

2 Answers2