Question regarding many-many relationships

Question

Hi,

I have a very simple problem for which though I have a solution but I want to know how others tackle it effectively.

Assume we have a User table, and a Role table. Each user can have multiple roles and vice-versa (many-to-many relationship). This relationship is stored in a cross table: UserRoleMapper (UserID, RoleID).

Now I have to get a list of all users, with their role names too. But if I have a user belonging to 3 different roles, I will get that user 3 times in a list. What is the best way to handle such scenarios? One simple way is to get all users first, and then fire another query (another hit to the DB) to get their respective roles. Is there any other solution better than this?

thanks,

Answer 1

So long as you just want a single row for every user - and string concatination for all their possible roles, you'll want to look at using a 'Cross Apply' and 'For XML Path' - this can all be done using a single query.

Plenty of examples here and here.

Answer 2

It is right that if a user has 3 roles that 3 results should be returned for the user and their roles with something like the following query

SELECT u.username, r.rolename
FROM user u
INNER JOIN
user_role ur
ON 
u.userid = ur.userid
INNER JOIN
role r
ON
ur.roleid = r.roleid

I'm assuming that your question refers to presenting this information? How you handle this in the UI is up to you. You may decide to use a query that concatenates roles for a user, or lazy load roles for a particular user only at the point when they are needed.

Answer 3

You can try to use a comma delimited list of the role ids, or names if required.

This will be determined by the db you use.

You cans use concatenation functions, group concats or wile loops.

i have found an interesting way to do this using sql server 2005

DECLARE @Table TABLE(
     ID INT,
     Val VARCHAR(50)
)
INSERT INTO @Table (ID,Val) SELECT 1, 'A'
INSERT INTO @Table (ID,Val) SELECT 1, 'B'
INSERT INTO @Table (ID,Val) SELECT 1, 'C'
INSERT INTO @Table (ID,Val) SELECT 2, 'B'
INSERT INTO @Table (ID,Val) SELECT 2, 'C'

--Concat
SELECT  t.ID,
     (
      SELECT tIn.Val + ','
      FROM @Table tIn
      WHERE tIn.ID = t.ID
      FOR XML PATH('')
     )
FROM    @Table t
GROUP BY t.ID