Need help with a query 1

[WBH1138]

Hi

I'd like to know if what I'm trying to achieve is possible.

I have 2 tables

A user group table consisting of...

UserGroupID
Description

and

a user group members table consisting of...

UserGroupID (as above)
UserID


Want I want returned is a list of all the UserGroups with a flag to show which groups contain a certain user

i.e.
UserGroupID Description UserX
---------------------------------
1 Group1 No
2 Group2 Yes
3 Group3 Yes

Hope that's clear.

Thanks in advance

 

[JohnDTampaBay]

Give this a shot... good luck.

DECLARE @UserID int -- or whatever
SET @UserID = 123 -- or whatever

SELECT ug.UserGroupID, ug.Description,
  CASE WHEN ugm.UserID IS NOT NULL THEN 'Yes'
  ELSE 'No' END AS UserX
FROM UserGroup AS ug
  LEFT OUTER JOIN UserGroupMember AS ugm
  ON ug.UserGroupID = ugm.UserGroupID
  AND ugm.UserID = @UserID

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[WBH1138]

Brilliant.

Many thanks.

I was almost there but was using

WHERE ugm.UserID = @UserID

instead of

AND ugm.UserID = @UserID