Solved

SQL query not giving the intended results

Posted on 2010-11-29
12
346 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now