Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL query not giving the intended results

Posted on 2010-11-29
12
Medium Priority
?
359 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
Independent Software Vendors: 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 1000 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

Independent Software Vendors: 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!

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

876 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