Collating results of subqueries into fields

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,687 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,207 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 610 | Replies: 2

•

Join Date: May 2008

Posts: 3

Reputation: Paul Phillips is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

Paul Phillips Paul Phillips is offline

Offline

Newbie Poster

Collating results of subqueries into fields

May 16th, 2008

Hi Guys,

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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')
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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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')
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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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

Last edited by peter_budo : May 16th, 2008 at 6:36 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: Collating results of subqueries into fields

May 19th, 2008

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...

Last edited by tesuji : May 19th, 2008 at 7:32 am.

•

Join Date: May 2008

Posts: 3

Reputation: Paul Phillips is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

Paul Phillips Paul Phillips is offline

Offline

Newbie Poster

Re: Collating results of subqueries into fields

May 19th, 2008

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) :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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?

Last edited by peter_budo : May 19th, 2008 at 12:46 pm. Reason: Keep It Organized - please use [code] tags. No offence but code tags are there for reason