Collating results of subqueries into fields

[Paul Phillips]

Hi Guys,

I'm new here and have come across a perplexing problem. I have a user table

CREATE TABLE Users
(
     UserID VARCHAR(64),
     UserName VARCHAR(50)
)

INSERT INTO Users (UserName) VALUES ('Tom')
INSERT INTO Users (UserName) VALUES ('Dick')
INSERT INTO Users (UserName) VALUES ('Harry')

Each user has roles and groups.

CREATE TABLE Roles
(
     RoleID VARCHAR(64),
     RoleName VARCHAR(50)
)

INSERT INTO Roles (RoleName) VALUES ('Noob')
INSERT INTO Roles (RoleName) VALUES ('Average')
INSERT INTO Roles (RoleName) VALUES ('Admin')

CREATE TABLE Groups
(
     GroupID VARCHAR(64),
     GroupName VARCHAR(50)
)

INSERT INTO Groups (GroupName) VALUES ('Fishing')
INSERT INTO Groups (GroupName) VALUES ('Running')
INSERT INTO Groups (GroupName) VALUES ('Base Jumping')

Linked via a couple of reference tables

CREATE TABLE UsersRoles
(
     UserID VARCHAR(64),
     RoleID VARCHAR(64)
)

INSERT INTO UsersRoles (UserID,RoleID) VALUES (1,1)
INSERT INTO UsersRoles (UserID,RoleID) VALUES (1,2)
INSERT INTO UsersRoles (UserID,RoleID) VALUES (1,3)
INSERT INTO UsersRoles (UserID,RoleID) VALUES (2,1)
INSERT INTO UsersRoles (UserID,RoleID) VALUES (3,1)
INSERT INTO UsersRoles (UserID,RoleID) VALUES (3,3)

CREATE TABLE UsersGroups
(
     UserID VARCHAR(64),
     GroupID VARCHAR(64)
)

INSERT INTO UsersGroups (UserID,GroupID) VALUES (1,1)
INSERT INTO UsersGroups (UserID,GroupID) VALUES (1,2)
INSERT INTO UsersGroups (UserID,GroupID) VALUES (2,2)
INSERT INTO UsersGroups (UserID,GroupID) VALUES (3,1)
INSERT INTO UsersGroups (UserID,GroupID) VALUES (3,3)

Now the tricky part

I am trying to get out a recordset along the lines of:

User Name Roles Groups
======= ================= =====================
Tom Noob, Average, Admin Fishing, Running
Dick Noob Running
Harry Noob, Admin Fishing, Base Jumping

Due to the constraints of Crystal Reports I can not use user defined SQL Functions. Can anybody help?

Kind regards,

Paul

[tesuji]

First of all, you must add PRIMARY keys and FOREIGN keys as well.
I don't know of crystal's limitations. Using standard sql you can clue those tables together by join operations, like this select * from A join B on A.field = B.field...
If crystal is unable to understand modern joins you can also use tradition inner joins like
select * from A, B where A.field = B.field...

[Paul Phillips]

No offense, Friend, but your answer is waffle.

Identifying primary and foreign keys only helps to speed up the engine and enforce a level of data integrity. Neither apply to my problem.

The way I thought I could do it is like this (I know the names are different - I wanted to keep it simple for the example) :

CREATE TABLE #TempPPUser
(
    UserID VARCHAR(64),
    Groups VARCHAR(8000),
    Roles VARCHAR(8000)
)

INSERT INTO #TempPPUser
SELECT tps_guid UserID, '' as Groups, '' as Roles FROM tps_user

DECLARE @UserID varchar(64), @GroupID varchar(64), @RoleID varchar(64)

DECLARE usersgroups_cursor CURSOR FOR
SELECT tps_user_id, tps_group_id
FROM tps_user_group

OPEN usersgroups_cursor

FETCH NEXT FROM usersgroups_cursor
INTO @UserID, @GroupID

UPDATE #TempPPUser
SET Groups = COALESCE(Groups + ',', '') + (select tps_group.tps_name from tps_group where tps_guid = @GroupID)
WHERE #TempPPUser.UserID = @UserID

WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM usersgroups_cursor
   INTO @UserID, @GroupID

   UPDATE #TempPPUser
   SET Groups = COALESCE(Groups + ',', '') + (select tps_group.tps_name from tps_group where tps_guid = @GroupID)
   WHERE #TempPPUser.UserID = @UserID
END

CLOSE usersgroups_cursor
DEALLOCATE usersgroups_cursor

DECLARE usersroles_cursor CURSOR FOR
SELECT tps_user_id, tps_role_id
FROM tps_user_role

OPEN usersroles_cursor

FETCH NEXT FROM usersroles_cursor
INTO @UserID, @RoleID

UPDATE #TempPPUser
SET Roles = COALESCE(Roles + ',', '') + (select tps_role.tps_name from tps_role where tps_guid = @RoleID)
WHERE #TempPPUser.UserID = @UserID

WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM usersroles_cursor
   INTO @UserID, @RoleID

   UPDATE #TempPPUser
   SET Roles = COALESCE(Roles + ',', '') + (select tps_role.tps_name from tps_role where tps_guid = @RoleID)
   WHERE #TempPPUser.UserID = @UserID
END

CLOSE usersroles_cursor
DEALLOCATE usersroles_cursor

select tps_user.tps_name, #TempPPUser.Groups, #TempPPUser.Roles
from tps_user INNER JOIN #TempPPUser ON tps_user.tps_guid = #TempPPUser.UserID 
where tps_name = 'PPhillips1'

DROP TABLE #TempPPUser

END

=======================

The problem is it takes forever to run! Anyone any better ideas?