One to many question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

One to many question

Author

Topic

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 12:51:12

This must be a common problem, so I'm just looking for what the best way to deal with the issue.

For example:
I have 3 tables.
Table A is User Information
Table B is Security Groups
Table C is Office Locations

A user can be in any number of security groups and office locations. If I query for all of their information in a single query, I get 1 row for each combination. If I use a looping query, I end up querying the database 3 times for each user.

In a perfect world, I need to pass a single recordset to a report where the security groups and office locations are listed in a single field as text.

Which method would be best for performance as the database grows? Is there anything that can simplify this process?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 13:18:33

What is a "looping" query?

SELECT TableA.*, TableB.*, TableC.*
FROM TableA
INNER JOIN TableB ON TableB.Col = TableA.Col
INNER JOIN TableC ON TableC.Col = TableA.Col

E 12°55'05.25"
N 56°04'39.16"

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 13:26:56

Sorry...I was thinking more programatically from my application.

When I run your query (or the similar ones I've tried) I get sample output like this:

Bob, Admin, Chicago
Bob, Admin, Michigan
Joe, Admin, Chicago
Joe, User, Chicago
Joe, Other, Chicago

What I'm looking for is how to deal with getting all of a particular user's groups and locations into a report so that each user only has 1 record with multiple values for those fields.

Can this be done through a query, or do I have to handle it through programming logic?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 13:29:36

How would you like it to be?
Can I decide for you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 13:31:07

[code]Bob | Admin | Chicago, Michigan
Joe | Admin, Other, User | Chicago[/code]

E 12°55'05.25"
N 56°04'39.16"

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 13:34:14

I would prefer to handle it in a single query.

What I was trying to avoid was:

SELECT * FROM Users

Then going through each user record and having an additional query for groups and locations and using my programming combine the text values and put them into a larger recordset to use for the report.

So in a sense, yes, please choose for me. I would even go as far as to suggestions if there's a better way to store the data. I just can't imagine that 3 individual queries per user record is the best way to go.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 13:34:39

SELECT User, dbo.fnSecGrpConcat(User), dbo.fnOfficeConcat(User)
FROM TableA
ORDER BY User

Concatenation function are plenty here at SQLTeam. Please feel free to search.

E 12°55'05.25"
N 56°04'39.16"

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 13:34:51

quote:
Originally posted by Peso

Bob | Admin | Chicago, Michigan
Joe | Admin, Other, User | Chicago

E 12°55'05.25"
N 56°04'39.16"

Yes, that output would be what I'm looking for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 13:45:15

CREATE FUNCTION dbo.fnSecGrpConcat
(
@User Name
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @S VARCHAR(8000)

SET @S = ''

SELECT @s = @s + ',' + d.SecGrpName
FROM (
select distinct top 100 percent secgrpname from securitygrouptable order by secgrpname
) as d

RETURN CASE WHEN @s = '' THEN null else STUFF(@s, 1, 0, '')
end

Now make a similar concatenation function for the offices
and type

SELECT User, dbo.fnSecGrpConcat(User), dbo.fnOfficeConcat(User)
FROM TableA
ORDER BY User

E 12°55'05.25"
N 56°04'39.16"

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 22:42:32

OK, I've tried adapting this to my specific need and it simply isn't working...I can't even get past syntax errors, so I must be doing something wrong. Here's the specific layout of a generic record in the database:

There's 5 tables:
tblUsers (3 fields: id, FirstName, LastName)
tblGroups (2 fields: id, GroupName)
tblOffices (2 fields: id, OfficeName)
tblGroupMatch (2 fields: UserID, GroupID)
tblOfficeMatch (2 fields: UserID, OfficeID)

Sample Data:
tblUsers:
1, 'Joe', 'Smith'

tblGroups:
1, 'Admin'
2, 'User'
3, 'Other'

tblOffices:
1, 'Chicago'
2, 'Detroit'
3, 'New York

tblGroupMatch:
1,1
1,2
1,3

tblOfficeMatch:
1,3
1,1

What I would like to do is be able to use a function/query combination to return:

1, 'Joe', 'Smith', 'Admin, User, Other', 'New York, Chicago'

Also...if the problem is in the way I'm storing my data I'm open to changing that too, it was simply the way we decided to do it and I've stuck with it up until now.

morleyz
Starting Member

17 Posts

Posted - 2007-08-27 : 23:30:55

OK...I finally got it. I found a few more relavent posts and managed to adjust a function to fit what I needed. Thanks for getting me down the right track!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources