Solved

SQL query not giving the intended results

Posted on 2010-11-29
12
350 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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