Thursday, March 22, 2012

Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

No comments:

Post a Comment