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,





Chris