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