Getting distinct users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting distinct users

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 10:23:34

Hi,

My sql statement includes an 'OR' which duplicates the returned values. The example just simplifies what I'm working on:

select
distinct [useraccount].userid,
From
TableB
inner join .....
inner join .....
inner join [ATable] on [ATableD].[userid] = [ATableD].[userid]
where ATableId = 1
or ATableID = 4

If I disregard the last line I get 13000 rows but if I include it I get nearly double because TableId in most cases is 1 or 4. How do I just get the distinct userid's?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:34:06

Can you show us the entire query? and the results that you are getting.
I'm seeing

distinct [useraccount].userid

But I dont see the table [useraccount] anywhere in your query?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 10:35:43

quote:
Originally posted by vijayisonly

Can you show us the entire query? and the results that you are getting.
I'm seeing
distinct [useraccount].userid
But I dont see the table [useraccount] anywhere in your query?

Sure, here is the full query...I've ordered by userid to physically see the duplications:

select
distinct [useraccount].userid, case when ProfessionalIdentifierTypeid = '1' then 1 else 0 end as GMC, case when ProfessionalIdentifierTypeid = '4' then 1 else 0 end as Pharbase
from
userrole
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
where professionalidentifiertypeid = 1
or professionalidentifiertypeid = 4
order by userid

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:37:23

And what is the result of this query?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 10:43:35

quote:
Originally posted by vijayisonly

And what is the result of this query?

userid..................................GMC.....Pharbase
F1A15B78-DABC-4D38-B4A4-0001692C6152....0.......1
F1A15B78-DABC-4D38-B4A4-0001692C6152 1 0
93F8613F-3A03-466F-9325-0004A6D8088E 0 1
93F8613F-3A03-466F-9325-0004A6D8088E 1 0
57F5C6E9-D801-46AD-BFAA-0009E14CE0A1 0 1
57F5C6E9-D801-46AD-BFAA-0009E14CE0A1 1 0
FFF78CE9-CE5A-4A46-B0DE-000F0E10C351 0 1
FFF78CE9-CE5A-4A46-B0DE-000F0E10C351 1 0

You can see that there are duplicates here the result should be:

F1A15B78-DABC-4D38-B4A4-0001692C6152....1.......1
93F8613F-3A03-466F-9325-0004A6D8088E 1 1
57F5C6E9-D801-46AD-BFAA-0009E14CE0A1 1 1
FFF78CE9-CE5A-4A46-B0DE-000F0E10C351 1 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:46:02

[code]F1A15B78-DABC-4D38-B4A4-0001692C6152....0.......1
F1A15B78-DABC-4D38-B4A4-0001692C6152 1 0[/code]
These two rows have different values for GMC and Pharbase, even though the id is the same. Which row do you need in the output?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 10:52:26

quote:
Originally posted by vijayisonly

F1A15B78-DABC-4D38-B4A4-0001692C6152....0.......1
F1A15B78-DABC-4D38-B4A4-0001692C6152 1 0

These two rows have different values for GMC and Pharbase, even though the id is the same. Which row do you need in the output?

I need just 1 row that shows like this:
F1A15B78-DABC-4D38-B4A4-0001692C6152 1 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:53:46

[code]select
[useraccount].userid,
max(case when ProfessionalIdentifierTypeid = '1' then 1 else 0 end) as GMC,
max(case when ProfessionalIdentifierTypeid = '4' then 1 else 0 end) as Pharbase
from
userrole
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
where professionalidentifiertypeid = 1
or professionalidentifiertypeid = 4
group by [useraccount].userid
order by userid[/code]

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 11:02:23

quote:

Originally posted by vijayisonly

select
[useraccount].userid, 
max(case when ProfessionalIdentifierTypeid = '1' then 1 else 0 end) as GMC, 
max(case when ProfessionalIdentifierTypeid = '4' then 1 else 0 end) as Pharbase
from 
userrole 
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073 
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
where professionalidentifiertypeid = 1 
or professionalidentifiertypeid = 4
group by [useraccount].userid
order by userid

Hi, thats awesome, thanks for that. I noticed you added max and grouped it by userid. Can you explain what you did so I can learn? from that?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 11:07:43

Sure Np.
Since you needed only row per user id, and wanted only the rows with '1's, I used a MAX(). MAX() function picks the maximum value for that column. Since we are grouping by userid, the maximum value for each user id is picked. Since its '1' for both GMC and Pharbase, you get both the values in a single row.
If you had wanted '0's in both the columns, you will just have to change MAX() to MIN().

Hope that makes sense?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 11:18:55

quote:
Originally posted by vijayisonly

Sure Np.
Since you needed only row per user id, and wanted only the rows with '1's, I used a MAX(). MAX() function picks the maximum value for that column. Since we are grouping by userid, the maximum value for each user id is picked. Since its '1' for both GMC and Pharbase, you get both the values in a single row.
If you had wanted '0's in both the columns, you will just have to change MAX() to MIN().

Hope that makes sense?

Hi yes that makes perfect sense thank you. My next task is to output the actual id's of the GMC and Pharbase which are held in another table. I will try to work that one out but can you tell me the first line of the select?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 11:21:51

I can't really tell much unless I see sample data and expected output.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 11:35:46

quote:
Originally posted by vijayisonly

I can't really tell much unless I see sample data and expected output.

I completely understand...I've just updated the last sql command to include the next (last) task. I've added the table professionalidentifiertype to the query. This table contains the GMC and Pharbase id's in a column called professionalidentifier. So the result would be:

userid......GMC......Pharbase
F1A15B78-DABC-4D38-B4A4-0001692C6152......3191309......10379816

I'm trying to work out what to use in the select statement instead of 'case'. This is my sql query which won't work because the first line won't allow the column professionalidentifier :

select [useraccount].userid, case when ProfessionalIdentifierTypeid = '1' then professionalidentifier else 0 end as GMC, case when [professionalidentifiertype].ProfessionalIdentifierTypeid = '4' then professionalidentifier else 0 end as Pharbase
from
userrole
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
inner join [professionalidentifiertype] on [professionalidentifiertype].[professionalidentifiertypeId] = [userprofessionalidentifier].[professionalidentifiertypeId]
where [professionalidentifiertype].professionalidentifiertypeid = 1 or [professionalidentifiertype].professionalidentifiertypeid = 4
group by [useraccount].userid

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 11:43:02

Are you getting an error with this?

select
[useraccount].userid, 
max(case when ProfessionalIdentifierTypeid = '1' then [professionalidentifiertype].professionalidentifier else 0 end) as GMC, 
max(case when ProfessionalIdentifierTypeid = '4' then [professionalidentifiertype].professionalidentifier else 0 end) as Pharbase
from 
userrole 
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073 
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
inner join [professionalidentifiertype] on [professionalidentifiertype].[professionalidentifiertypeId] = [userprofessionalidentifier].[professionalidentifiertypeId]
where [professionalidentifiertype].professionalidentifiertypeid = 1 or [professionalidentifiertype].professionalidentifiertypeid = 4
group by [useraccount].userid
order by userid

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 11:55:56

quote:

Originally posted by vijayisonly

Are you getting an error with this?

select
[useraccount].userid, 
max(case when ProfessionalIdentifierTypeid = '1' then [professionalidentifiertype].professionalidentifier else 0 end) as GMC, 
max(case when ProfessionalIdentifierTypeid = '4' then [professionalidentifiertype].professionalidentifier else 0 end) as Pharbase
from 
userrole 
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073 
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
inner join [professionalidentifiertype] on [professionalidentifiertype].[professionalidentifiertypeId] = [userprofessionalidentifier].[professionalidentifiertypeId]
where [professionalidentifiertype].professionalidentifiertypeid = 1 or [professionalidentifiertype].professionalidentifiertypeid = 4
group by [useraccount].userid
order by userid

yeah, it says invalid column name

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 12:03:56

which column name is invalid? Pls post the entire error.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-05-28 : 12:10:42

quote:
Originally posted by vijayisonly

which column name is invalid? Pls post the entire error.

The error is:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ProfessionalIdentifierTypeid'.

I've rewritten the first bit and it seems to work. Just for your information it is:

select
[useraccount].userid,
isNull([GMC].[professionalidentifier],'Not available') [GMC],
isNull([Pharbase].[professionalidentifier],'Not available') [Pharbase]
from
userrole
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
left join [userprofessionalidentifier] [GMC] on [GMC].[userid] = [useraccount].[userid] and [GMC].ProfessionalIdentifierTypeid = 1
left join [userprofessionalidentifier] [Pharbase] on [Pharbase].[userid] = [GMC].[userid] and [Pharbase].ProfessionalIdentifierTypeid = 4

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 12:14:45

This is fine. or you could do this I guess.

select
[useraccount].userid, 
max(case when [userprofessionalidentifier.ProfessionalIdentifierTypeid = '1' then [professionalidentifiertype].professionalidentifier else 0 end) as GMC, 
max(case when [userprofessionalidentifier.ProfessionalIdentifierTypeid = '4' then [professionalidentifiertype].professionalidentifier else 0 end) as Pharbase
from 
userrole 
inner join [useraccount] on [useraccount].[userid] = [userrole].[userid] and accountstatusid = 1 -- active
inner join [role] on [role].[roleid] = [userrole].[roleid] and [role].[roleid] = '19F05FAA-6F9B-4139-8A2B-B86D87F4CB13'--GP's 20376
inner join [useroptin] on [useroptin].[userid] = [useraccount].[userid] and optinid = 2 --opted to email 17073 
inner join [Useremailaddress] on [Useremailaddress].UserID = [useraccount].[UserID] and hasbounced = 0 -- mailaddress not bouncing back -- 13165
inner join [userprofessionalidentifier] on [userprofessionalidentifier].[userid] = [useraccount].[userid]
inner join [professionalidentifiertype] on [professionalidentifiertype].[professionalidentifiertypeId] = [userprofessionalidentifier].[professionalidentifiertypeId]
where [professionalidentifiertype].professionalidentifiertypeid = 1 or [professionalidentifiertype].professionalidentifiertypeid = 4
group by [useraccount].userid
order by userid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources