Incorrect # of records resulting from Left outter join

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
JpLeBlancAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Well, without solving the issue, you could add a DISTINCT to the select.
0
BrandonGalderisiCommented:
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
ErnariashCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JpLeBlancAuthor Commented:
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
JpLeBlancAuthor Commented:
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
JpLeBlancAuthor Commented:
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
ErnariashCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.