Display flag if record exists in table

[dmorand]

I have three tables (hsusers,hscovg roupmember,hsco vgroup). I want to display all users, and a flag 'IsCoverageGrou pMember' if the user is a member of at least 1 coverage group.

Here is a definition of the tables:

hsusers = list of users
hscovgroupmembe r = 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 'IsCoverageGrou pMember' 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 'IsCoverageGrou pMember' 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

[iburyak]

Try this:

select u.userdescription as 'username', 
       u.loginid 'login id',
       (case  
		  when max(cgm.objectid) is not null Then 'Y' 
		  else 'N' 
		end) as IsCoverageGroupMember 
from hsuser u 
left outer join hscovgroupmember cgm 
    on u.objectid = cgm.user_oid 
left outer join hscovgroup cg 
    on cgm.covgroup_oid = cg.objectid 
group by u.loginid,u.userdescription
order by u.userdescription

Good Luck.

[dmorand]

Thanks, that worked like a charm!!