Distinct within a joined subquery

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

Transact-SQL (2000)

Distinct within a joined subquery

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 12:30:28

I have the following two tables...

Users (user_id)
UserGroupRoles (user_id, group_id, role_id)

there is an input parameter @roleId

The following is part of subquery I have in a stored Procedure that joins these two tables

SELECT *...Outer portion of StoredProcedure

FROM (dbo.[Users] u

LEFT OUTER JOIN dbo.[UserGroupRoles] ugr ON (ugr.user_id=u.user_id))

WHERE ugr.role_id=@roleId

...Rest of Stored Procedure

The problem with this subquery is that there may be multiple records in the UserGroupRoles table that have the same user_id and role_id. In other words, the Join may return more than one record and the only difference will be the group_id. I do not need to distinguish by group_id, and returning more than one record here will mess things up for the outer portions of my stored porcedure. I only want one joined record to be returned. Can anyone help?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:03:12

You can use TOP 1 together with an ORDER BY.

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 13:11:07

Can you please explain? I'm not sure where the TOP 1 fits in. I need the join to obviously return more than one record... but only one for each join match (as opposed to the multiple records that are likely to exist).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:16:03

What does this mean then?

quote:
I do not need to distinguish by group_id, and returning more than one record here will mess things up for the outer portions of my stored porcedure. I only want one joined record to be returned. Can anyone help?

Do you have same sample data and the expected output of that data?

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 13:26:58

Sorry, I may have made a confusing statement. Here is some sample data

Users: 3 records...
"User1"
"User2"
"User3"

UserGroupRoles: 5 records...

"User1, GroupA, Administrator"
"User2, GroupA, Director"
"User2, GroupB, Administrator"
"User2, GroupB, Director",
"User3, GRoupA, Director"

Right now the SubQuery Returns (roleId
='Director'):

"User2, GroupA, Director"
"User2, GroupB, Director"
"User3, GroupA, Director"

I need it to return:

"User2, GroupA, Director"
"User3, GroupA, Director"

The record ("User2, GroupB, Director") is the same as the one I want, other than the group which is irrelevant to me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:31:57

Why is "User2, GroupB, Director" removed? What is the logic?
Why not remove "User2, GroupA, Director" instead, and return

"User2, GroupB, Director"
"User3, GroupA, Director"

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:33:14

So you want to return all users associated with the role "director", and also a random valid group for that user (no matter which)?

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 13:34:40

yes, I don't care which "Group" record is returned.

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 13:35:39

frankly, I don't need the group_id field returned at all in the subquery

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:41:50

SELECT DISTINCT User_ID FROM UserGroupRoles WHERE Role_ID = @Role_ID

will do, since you have the roleid already, because that is what you search for...

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 13:50:35

Where does this fit into my JOIN Statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:55:03

I do not know. We do not know. We have only seen a part of it.
Give us the whole query and I am sure we will find something clever out!
Maybe a WHERE .. IN will be a smarter choice?

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 14:04:33

The whole query is pretty darn ugly because it is used as part of a stored procedure that can page records, however here it is. I also simplified the table structure in my original example. As you can see, the subquery (shown in red) is where I need to limit the returned records to avoid the duplicates)

SELECT Result.UserId,
Result.UserStatusCode,
Result.username,
Result.PersonId,
Result.pwdhash,
Result.pwdsalt,
Result.pwdexpirationdate,
Result.resetpassword,
Result.pwdhint,
Result.pwdretrievalquestion,
Result.pwdretrievalanswer,
Result.primarygroup_id,
Result.RowNumber,
p.person_id as PersonId,
p.firstname,
p.lastname FROM
(SELECT TOP (100) PERCENT *
FROM (SELECT u.user_id as UserId,
u.status_cd AS UserStatusCode,
u.username,
u.person_id as PersonId,
u.pwdhash,
u.pwdsalt,
u.pwdexpirationdate,
u.resetpassword,
u.pwdhint,
u.pwdretrievalquestion,
u.pwdretrievalanswer,
u.primarygroup_id,
gmr.role_id,
ROW_NUMBER() OVER (ORDER BY username) AS RowNumber

FROM (dbo.[Users] u LEFT OUTER JOIN dbo.[GroupMemberRoles] gmr ON (gmr.member_id=u.user_id))
WHERE gmr.role_id='DIR') AS Selected) AS Result

LEFT JOIN dbo.[Persons] p ON (p.person_id=Result.PersonId)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 14:13:44

You can't with the syntax you have.
Try this

SELECT		u.user_id as UserId,
		u.status_cd AS UserStatusCode,
		u.username, 
		u.person_id as PersonId,
		u.pwdhash,
		u.pwdsalt,
		u.pwdexpirationdate,
		u.resetpassword,
		u.pwdhint,
		u.pwdretrievalquestion,
		u.pwdretrievalanswer,
		u.primarygroup_id, 
		gmr.role_id, 
		ROW_NUMBER() OVER (ORDER BY username) AS RowNumber 
FROM		dbo.[Users] u
INNER JOIN	dbo.[GroupMemberRoles] gmr ON gmr.member_id = u.user_id and gmr.role_id = 'DIR'
INNER JOIN	dbo.[Persons] p ON p.person_id = u.PersonId

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 14:41:19

That still returns two records if there are two records in the GroupMemberRoles table that match the criteria for user_id and role_id)

Right now I have a subquery of form

SELECT * FROM (x)

where X is the results of (TableA INNER JOIN TableB ON (Whatever))

Is there a way to alter X so that it only returns the results for distinct user_id and role_id columns (combined)?

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 14:44:32

It would be great if I could do something like:

SELECT * FROM
(SELECT DISTINCT colA, colb FROM
(Table A INNER JOIN Table B ON (whatever)))

But this does not work... ie it does not compile

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 14:47:31

Add an alias after the last paranthesis.

SELECT * FROM
(SELECT DISTINCT colA, colb FROM
TableA INNER JOIN TableB ON whatever) x

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 15:03:37

Thanks, that takes care of the compilation issue.. however there is one last problem. The SELECT DISTINCT colA, colB makes it so the resultant records only have colA and colB in them and I obviously need all the other columns that are part of the JOIN. Is there a way to make it so DISTINCT works, but returns columns in addition to those it uses to test for uniqueness?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 15:11:01

Did you try my suggestion posted 14:13:44, with DISTINCT?

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2006-09-13 : 15:14:36

Where does DISTINCT go?

SELECT u.user_id as UserId,
u.status_cd AS UserStatusCode,
u.username,
u.person_id as PersonId,
u.pwdhash,
u.pwdsalt,
u.pwdexpirationdate,
u.resetpassword,
u.pwdhint,
u.pwdretrievalquestion,
u.pwdretrievalanswer,
u.primarygroup_id,
gmr.role_id,
ROW_NUMBER() OVER (ORDER BY username) AS RowNumber
FROM dbo.[Users] u
INNER JOIN dbo.[GroupMemberRoles] gmr ON gmr.member_id = u.user_id and gmr.role_id = 'DIR'
INNER JOIN dbo.[Persons] p ON p.person_id = u.PersonId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 15:17:19

Between SELECT and u.user_id as UserId

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources