I have three tables (hsusers,hscovgroupmember,hscovgroup). I want to display all users, and a flag 'IsCoverageGroupMember' if the user is a member of at least 1 coverage group.
Here is a definition of the tables:
hsusers = list of users
hscovgroupmember = contains users and the coverage groups they are assigned to
hscovgroup = contains the names of the coverage groups
I can run the below query, and I get a listing of users and coverage groups they have assigned. The problem is the user is being listed numerous times. If they are assigned to 5 coverage groups, there are 5 rows.
-
select u.userdescription as 'username',
-
u.loginid 'login id',cg.groupname
-
from hsuser u with (nolock)
-
left outer join hscovgroupmember cgm with (nolock)
-
on u.objectid = cgm.user_oid
-
left outer join hscovgroup cg with (nolock)
-
on cgm.covgroup_oid = cg.objectid
-
order by u.userdescription
-
I want to only have the user listed once, and a flag 'IsCoverageGroupMember' set to 'Y' or 'N' if they are a member of a group.
I've tried the below query, but am wondering if there's a better way I should do this. The 'IsCoverageGroupMember' field is only 1 of multiple other fields I need to have in the query. This is just a portion of the query, as I'm doing a data extract to load into an external system. I have a few other 'IsMember' type columns that I'll need as well.
-
select u.userdescription as 'username',
-
u.loginid 'login id',(case
-
when cgm.objectid is not null Then 'Y'
-
else 'N'
-
end) as IsCoverageGroupMember
-
from hsuser u with (nolock)
-
left outer join hscovgroupmember cgm with (nolock)
-
on u.objectid = cgm.user_oid
-
group by u.loginid,u.userdescription,
-
(case
-
when cgm.objectid is not null Then 'Y'
-
else 'N'
-
end)
-
order by u.userdescription
-
