Solved

SQL query not giving the intended results

Posted on 2010-11-29
12
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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