Solved

SQL query not giving the intended results

Posted on 2010-11-29
12
351 Views
Last Modified: 2012-05-10
SELECT DISTINCT s.Userid
  FROM [dbProductSubscription].[dbo].[tblSubscriptions] s
  JOIN [dbProductSubscription].[dbo].[tblAddresses] a WITH (NOLOCK) on s.UserID=a.UserID
  JOIN [dbProductSubscription].[dbo].[tblStates] e WITH (NOLOCK) on e.StateId=a.StateId
  WHERE s.IsActive=1

This query gives 21786 rows, which is correct.

When I add the following to the query, the number of rows goes up (31044 rows) and I can't figure out why.

SELECT DISTINCT s.UserId
        ,a.AddressName
      ,a.Address1
      ,a.Address2
      ,a.City
      ,e.Abbreviation
      ,a.PostalCode
      ,a.Phone
  FROM [dbProductSubscription].[dbo].[tblSubscriptions] s
  JOIN [dbProductSubscription].[dbo].[tblAddresses] a WITH (NOLOCK) on s.UserID=a.UserID
  JOIN [dbProductSubscription].[dbo].[tblStates] e WITH (NOLOCK) on e.StateId=a.StateId
  WHERE s.IsActive=1


Can someone point me in the right direction? What am i doing wrong?
0
Comment
Question by:jmicorp
12 Comments
 

Author Comment

by:jmicorp
ID: 34234613
I  added this accidentally to the C# zone. Sorry about that.
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34234617
because a user may have 2 addresses or an address may have 2 state
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34234694
select s.UserId
      ,a.AddressName
      ,a.Address1
      ,a.Address2
      ,a.City
      ,e.Abbreviation
      ,a.PostalCode
      ,a.Phone
FROM
(
SELECT *, rn=ROW_NUMBER() over (partition by s.UserId order by s.UserId)
  FROM [dbProductSubscription].[dbo].[tblSubscriptions] s
  JOIN [dbProductSubscription].[dbo].[tblAddresses] a WITH (NOLOCK) on s.UserID=a.UserID
  JOIN [dbProductSubscription].[dbo].[tblStates] e WITH (NOLOCK) on e.StateId=a.StateId
WHERE s.IsActive=1
) X
Where rn=1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jmicorp
ID: 34234948
I am getting the following error with the query above:

Msg 8156, Level 16, State 1, Line 1
The column 'CreateDate' was specified multiple times for 'X'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.UserId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.AddressName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.Address1" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.Address2" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.City" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "e.Abbreviation" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.PostalCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.Phone" could not be bound.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34234988
Sorry.  Don't have tables to test against.

select UserId
      ,AddressName
      ,Address1
      ,Address2
      ,City
      ,Abbreviation
      ,PostalCode
      ,Phone
FROM
(
SELECT s.UserId
      ,a.AddressName
      ,a.Address1
      ,a.Address2
      ,a.City
      ,e.Abbreviation
      ,a.PostalCode
      ,a.Phone
      ,rn=ROW_NUMBER() over (partition by s.UserId order by s.UserId)
  FROM [dbProductSubscription].[dbo].[tblSubscriptions] s
  JOIN [dbProductSubscription].[dbo].[tblAddresses] a WITH (NOLOCK) on s.UserID=a.UserID
  JOIN [dbProductSubscription].[dbo].[tblStates] e WITH (NOLOCK) on e.StateId=a.StateId
WHERE s.IsActive=1
) X
Where rn=1
0
 
LVL 10

Expert Comment

by:wls3
ID: 34235378
Have you tried a query like

select count(s.userid)
,distinct s.userid
,...
group by s.userid

Open in new window


to determine where the multiples are coming from?
0
 

Author Comment

by:jmicorp
ID: 34235427
cyberkiwi,

This is worked perfectly. Could you briefly explain what this does?

  ,rn=ROW_NUMBER() over (partition by s.UserId order by s.UserId)    .........    Where rn=1

and also what is the "X"?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34235510
http://msdn.microsoft.com/en-us/library/ms186734.aspx

row_number() over (
  partition by ..    -- something like group by , this part is optional
  order by ....)   --

Rownumber produces a number for each row, going from 1 up based on the ORDER BY clause.
The Partition by clause is used to reset the row_number to 1 whenever the partition changes.
To see what it is doing, run this:

SELECT s.UserId
      ,a.AddressName
      ,a.Address1
      ,a.Address2
      ,a.City
      ,e.Abbreviation
      ,a.PostalCode
      ,a.Phone
      ,rn=ROW_NUMBER() over (partition by s.UserId order by s.UserId)
  FROM [dbProductSubscription].[dbo].[tblSubscriptions] s
  JOIN [dbProductSubscription].[dbo].[tblAddresses] a WITH (NOLOCK) on s.UserID=a.UserID
  JOIN [dbProductSubscription].[dbo].[tblStates] e WITH (NOLOCK) on e.StateId=a.StateId
WHERE s.IsActive=1
ORDER BY s.UserId

You will see how the numbering works.
This is then sub-queried, and filtered for just rn=1, which is only the first record from each partition, in our case, one record per s.UserId
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34235513
X could have been SQ, or SubQuery or Numbered or anything - it is just an alias given to the subquery.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34236077
>>This is worked perfectly.<<
I trust you realize that you are picking just one random address out of many for each userid.  That may be what you wanted on the hand it may be not what you expected.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34238704
It trying to get distinct UserIDs but when you add more columns how can query give distinct result because other columns are not distinct.
0
 

Author Closing Comment

by:jmicorp
ID: 34241270
Thanks a bunch! As always, you guys really know your stuff!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question