?
Solved

Incorrect # of records resulting from Left outter join

Posted on 2008-11-14
9
Medium Priority
?
437 Views
Last Modified: 2012-05-05
Hello all, hopefully my issue is a simple one.

PCS data-details table has 83315 records in it.
[Emp Data] is used to build hierarchy
View_ distinct NPA/NXX and cluster is used to add a market cluster based on the customers number.

here is my join statement.

FROM         dbo.[PCS Data - Details] LEFT OUTER JOIN
                      dbo.[EMP DATA] ON dbo.[PCS Data - Details].[Join Key] = dbo.[EMP DATA].[Join Key] LEFT OUTER JOIN
                      dbo.[View_Distinct NPA/NAA and cluster] ON SUBSTRING(dbo.[PCS Data - Details].ctn, 1, 6) = dbo.[View_Distinct NPA/NAA and cluster].[NPA/NXX]

as you can see i take the first 6 characters from the mobile number and match it up to the NPA/NXX from the NPA/nxx field in the market table. but my result is giving me more than double the records than i have so i am getting duplicated records.  The view that feeds the NPA/NXX is a distinct view based upon the same idea in a table that already has market clusters and i have verified that there are no suplicates in the view that does this calculattion.

Can anyone provide me with a better join that doesnt return so many duplicates?  I would like to show only the PCS data.

I am doing this from MSSQL within SQL Server Management Studio 2005.

You can see in the attached Picture all of the field in the three tables i am trying to use to make this work.

untitled.JPG
0
Comment
Question by:JpLeBlanc
7 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22962989
Well, without solving the issue, you could add a DISTINCT to the select.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22963043
Run these queries.  It will help you find your duplicates based upon your joined fields.
select [join key] from [pcs data - details] group by [join key] having count(*)>1
select [join key] from [EMP DATA] group by [join key] having count(*)>1
select left(ctn,6) from [pcs data - details] group by left(ctn,6) having count(*)>1
select [NPA/NXX] from [View_Distinct NPA/NAA and cluster] group by [NPA/NXX] having count(*)>1

Open in new window

0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22963117
We will need more details from you, it depend what you need from your query. A distinct could solve your problems
Select Distinct Column1, Column1..from yourFrom

But you could also use subqueries with group by see query.

SELECT *
FROM         dbo.[PCS Data - Details]
 LEFT OUTER JOIN       (
		SELECT  [Join Key], MAX(COLUNMNEED) COL1
		FROM dbo.[EMP DATA]
		GROUP BY [Join Key]
		) E ON dbo.[PCS Data - Details].[Join Key] = E.[Join Key]
LEFT OUTER JOIN (
			SELECT  [NPA/NXX], MAX(COLUNMNEED) COL1
			FROM dbo.[View_Distinct NPA/NAA and cluster]
			GROUP BY [NPA/NXX]
	  ) V
                      ON SUBSTRING(dbo.[PCS Data - Details].ctn, 1, 6) = V.[NPA/NXX]

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JpLeBlanc
ID: 22963441
Additional information was requested. I hope im providing what you guys need.

All i am looking to do is attach the cluster field from distinct [NPA/NXX] table to the end of each record on the {PCS data] table.  I believe a nested join is what i am looking for however nested joins seem to elude me.  for some reason they dont make any sense to me :(

Hope this helps  
0
 

Author Comment

by:JpLeBlanc
ID: 22963675
BrandonGalderisi:

I ran those selects you asked about...

select [join key] from [pcs data - details] group by [join key] having count(*)>1
This returns duplicates but that is ok as some people can get multiple records each month the key is used to join people to the emp data table to build heirarchy within the company.

select [join key] from [EMP DATA] group by [join key] having count(*)>1
there are no duplicates here.

select left(ctn,6) from [pcs data - details] group by left(ctn,6) having count(*)>1
This one will also have duplicates as there are multiple customers with the mobile numbers in the same NPA/NXX

select [NPA/NXX] from [View_Distinct NPA/NAA and cluster] group by [NPA/NXX] having count(*)>1
there are duplicates in this query and i am not sure why as the view that i have is based on the following code...
SELECT DISTINCT SUBSTRING(Mobile, 1, 6) AS [NPA/NXX], Cluster
FROM         dbo.[Wave Data - Details]


The NPA/NXX is the area code and prefix of a phone number

0
 

Author Comment

by:JpLeBlanc
ID: 22963740
ok i put in a distinct... i am most likely doing something wrong because i am still showing up with the 174k records instead of the 83k i should have.  
SELECT DISTINCT 
                      dbo.[PCS Data - Details].uniquekey, dbo.[PCS Data - Details].timestamp, dbo.[PCS Data - Details].center_name, dbo.[PCS Data - Details].q1, 
                      dbo.[PCS Data - Details].q2, dbo.[PCS Data - Details].q3, dbo.[PCS Data - Details].q4, dbo.[PCS Data - Details].q5, dbo.[PCS Data - Details].q6, 
                      dbo.[PCS Data - Details].q6_transfer, dbo.[PCS Data - Details].q7, dbo.[PCS Data - Details].q8, dbo.[PCS Data - Details].avg_score, 
                      dbo.[PCS Data - Details].fcr, dbo.[PCS Data - Details].region_name, dbo.[PCS Data - Details].survey_type, dbo.[PCS Data - Details].ctn, 
                      dbo.[PCS Data - Details].calltype, dbo.[PCS Data - Details].agentlname, dbo.[PCS Data - Details].agentfname, dbo.[PCS Data - Details].agent_id, 
                      dbo.[EMP DATA].mgrLast + N', ' + dbo.[EMP DATA].mgrFirst AS [MGR Name], 
                      dbo.[EMP DATA].amgrLast + N', ' + dbo.[EMP DATA].amgrFirst AS [AM Name], dbo.[EMP DATA].dLast + N', ' + dbo.[EMP DATA].dFirst AS [Director Name], 
                      dbo.[PCS Data - Details].[Join Key], dbo.[EMP DATA].[Join Key] AS Expr1, dbo.[PCS Data - Details].username, 
                      dbo.[View_Distinct NPA/NAA and cluster].Cluster, dbo.[View_Distinct NPA/NAA and cluster].[NPA/NXX]
FROM         dbo.[PCS Data - Details] LEFT OUTER JOIN
                      dbo.[EMP DATA] ON dbo.[PCS Data - Details].[Join Key] = dbo.[EMP DATA].[Join Key] LEFT OUTER JOIN
                      dbo.[View_Distinct NPA/NAA and cluster] ON SUBSTRING(dbo.[PCS Data - Details].ctn, 1, 6) = dbo.[View_Distinct NPA/NAA and cluster].[NPA/NXX]

Open in new window

0
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22964707

You have duplicates on your main table PCS data-details table has 83315 records in it:
Your key to join to
[View_Distinct NPA/NAA and cluster] is SUBSTRING(dbo.[PCS Data - Details].ctn, 1, 6)  is not unique. (This one will also have duplicates as there are multiple customers with the mobile numbers in the same NPA/NXX ). You will need to make a decision about witch Cluster and [NPA/NXX] to show. Please try the query to see if that is the problem...

SELECT                dbo.[PCS Data - Details].uniquekey,
                      dbo.[PCS Data - Details].timestamp, 
                      dbo.[PCS Data - Details].center_name, 
                      dbo.[PCS Data - Details].q1, 
                      dbo.[PCS Data - Details].q2, 
                      dbo.[PCS Data - Details].q3, 
                      dbo.[PCS Data - Details].q4, 
                      dbo.[PCS Data - Details].q5,
                      dbo.[PCS Data - Details].q6, 
                      dbo.[PCS Data - Details].q6_transfer,
                      dbo.[PCS Data - Details].q7, 
                      dbo.[PCS Data - Details].q8, 
                      dbo.[PCS Data - Details].avg_score, 
                      dbo.[PCS Data - Details].fcr, 
                      dbo.[PCS Data - Details].region_name, 
                      dbo.[PCS Data - Details].survey_type, 
                      dbo.[PCS Data - Details].ctn, 
                      dbo.[PCS Data - Details].calltype,
                      dbo.[PCS Data - Details].agentlname, 
                      dbo.[PCS Data - Details].agentfname, 
                      dbo.[PCS Data - Details].agent_id, 
                      dbo.[EMP DATA].mgrLast + N', ' + dbo.[EMP DATA].mgrFirst AS [MGR Name], 
                      dbo.[EMP DATA].amgrLast + N', ' + dbo.[EMP DATA].amgrFirst AS [AM Name], 
                      dbo.[EMP DATA].dLast + N', ' + dbo.[EMP DATA].dFirst AS [Director Name], 
                      dbo.[PCS Data - Details].[Join Key], dbo.[EMP DATA].[Join Key] AS Expr1, 
                      dbo.[PCS Data - Details].username, 
                      MAX(dbo.[View_Distinct NPA/NAA and cluster].Cluster) Cluster , 
                      MAX(dbo.[View_Distinct NPA/NAA and cluster].[NPA/NXX])[NPA/NXX]
FROM         dbo.[PCS Data - Details] LEFT OUTER JOIN
                      dbo.[EMP DATA] ON dbo.[PCS Data - Details].[Join Key] = dbo.[EMP DATA].[Join Key] 
                      LEFT OUTER JOIN
                      dbo.[View_Distinct NPA/NAA and cluster] ON SUBSTRING(dbo.[PCS Data - Details].ctn, 1, 6) = dbo.[View_Distinct NPA/NAA and cluster].[NPA/NXX]
GROUP BY 
     dbo.[PCS Data - Details].uniquekey,
                      dbo.[PCS Data - Details].timestamp, 
                      dbo.[PCS Data - Details].center_name, 
                      dbo.[PCS Data - Details].q1, 
                      dbo.[PCS Data - Details].q2, 
                      dbo.[PCS Data - Details].q3, 
                      dbo.[PCS Data - Details].q4, 
                      dbo.[PCS Data - Details].q5,
                      dbo.[PCS Data - Details].q6, 
                      dbo.[PCS Data - Details].q6_transfer,
                      dbo.[PCS Data - Details].q7, 
                      dbo.[PCS Data - Details].q8, 
                      dbo.[PCS Data - Details].avg_score, 
                      dbo.[PCS Data - Details].fcr, 
                      dbo.[PCS Data - Details].region_name, 
                      dbo.[PCS Data - Details].survey_type, 
                      dbo.[PCS Data - Details].ctn, 
                      dbo.[PCS Data - Details].calltype,
                      dbo.[PCS Data - Details].agentlname, 
                      dbo.[PCS Data - Details].agentfname, 
                      dbo.[PCS Data - Details].agent_id, 
                      dbo.[EMP DATA].mgrLast + N', ' + dbo.[EMP DATA].mgrFirst AS [MGR Name], 
                      dbo.[EMP DATA].amgrLast + N', ' + dbo.[EMP DATA].amgrFirst AS [AM Name], 
                      dbo.[EMP DATA].dLast + N', ' + dbo.[EMP DATA].dFirst AS [Director Name], 
                      dbo.[PCS Data - Details].[Join Key], dbo.[EMP DATA].[Join Key] AS Expr1, 
                      dbo.[PCS Data - Details].username

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

621 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