By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Display flag if record exists in table

100+
P: 219
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.

Expand|Select|Wrap|Line Numbers
  1. select u.userdescription as 'username',
  2. u.loginid 'login id',cg.groupname
  3. from hsuser u with (nolock)
  4. left outer join hscovgroupmember cgm with (nolock)
  5.     on u.objectid = cgm.user_oid
  6. left outer join hscovgroup cg with (nolock)
  7.     on cgm.covgroup_oid = cg.objectid
  8. order by u.userdescription
  9.  
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.

Expand|Select|Wrap|Line Numbers
  1. select u.userdescription as 'username',
  2. u.loginid 'login id',(case 
  3.     when cgm.objectid is not null Then 'Y'
  4.     else 'N'
  5.     end) as IsCoverageGroupMember
  6. from hsuser u with (nolock)
  7. left outer join hscovgroupmember cgm with (nolock)
  8. on u.objectid = cgm.user_oid
  9. group by u.loginid,u.userdescription,
  10. (case 
  11.     when cgm.objectid is not null Then 'Y'
  12.     else 'N'
  13.     end)
  14. order by u.userdescription
  15.  
May 11 '09 #1
Share this Question
Share on Google+
2 Replies


iburyak
Expert 100+
P: 1,017
Try this:

Expand|Select|Wrap|Line Numbers
  1. select u.userdescription as 'username', 
  2.        u.loginid 'login id',
  3.        (case  
  4.           when max(cgm.objectid) is not null Then 'Y' 
  5.           else 'N' 
  6.         end) as IsCoverageGroupMember 
  7. from hsuser u 
  8. left outer join hscovgroupmember cgm 
  9.     on u.objectid = cgm.user_oid 
  10. left outer join hscovgroup cg 
  11.     on cgm.covgroup_oid = cg.objectid 
  12. group by u.loginid,u.userdescription
  13. order by u.userdescription 
Good Luck.
May 11 '09 #2

100+
P: 219
Thanks, that worked like a charm!!
May 12 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.