SQL Duplicate records in statement compared to View

The following SQL Statement  creates twice as many records as the underlying View vPerformanceHolesDelays. What might I be doing wrong? Could it be duplicates in one of the tables?

SELECT vPerformanceHolesDelays.*, p1.Surname AS [Foreman Name], p2.Surname AS [Supervisor Name],  p3.Surname AS [Mach Operator], p4.Surname AS [Assist Operator], p5.Surname As [Assist Operator 2], p6.Surname As [Assist Operator 3]
FROM vPerformanceHolesDelays Left Join People AS p1 ON [vPerformanceHolesDelays].[Foreman] = p1.[Operator COY]  Left Join People AS p2 ON [vPerformanceHolesDelays].[Supervisor] = p2.[Operator COY] Left Join People AS p3 ON vPerformanceHolesDelays.[Machine Operator] = p3.[Operator COY]  Left Join People AS p4 ON [vPerformanceHolesDelays].[Assistant Operator] = p4.[Operator COY] Left Join People AS p5 ON vPerformanceHolesDelays.[Assistant Operator 2] = p5.[Operator COY]  Left Join People AS p6 ON [vPerformanceHolesDelays].[Assistant Operator 3] = p6.[Operator COY]

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Could it be duplicates in one of the tables?
most likely due to multiple records within the records that you're joining, which creating a multiply effect.

To remove these duplicate, either make sure the join condition is correct, try add group by / distinct in your select statement.

a quick fix could be:

SELECT distinct vPerformanceHolesDelays.*, p1.Surname AS [Foreman Name], p2.Surname AS [Supervisor Name],  p3.Surname AS [Mach Operator], p4.Surname AS [Assist Operator], p5.Surname As [Assist Operator 2], p6.Surname As [Assist Operator 3]
FROM vPerformanceHolesDelays Left Join People AS p1 ON [vPerformanceHolesDelays].[Foreman] = p1.[Operator COY]  Left Join People AS p2 ON [vPerformanceHolesDelays].[Supervisor] = p2.[Operator COY] Left Join People AS p3 ON vPerformanceHolesDelays.[Machine Operator] = p3.[Operator COY]  Left Join People AS p4 ON [vPerformanceHolesDelays].[Assistant Operator] = p4.[Operator COY] Left Join People AS p5 ON vPerformanceHolesDelays.[Assistant Operator 2] = p5.[Operator COY]  Left Join People AS p6 ON [vPerformanceHolesDelays].[Assistant Operator 3] = p6.[Operator COY]
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Great answer! Thanks a lot
0
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
SQL

From novice to tech pro — start learning today.