CODEWITHSUNDEEP
sqlt-sqlview
Brian D. Brubaker
Brian D. Brubaker

Reputation: 349

Incorrect Join in a View

I am creating a View in SQL Manager 2012 that has some Employee Details (name, costcenter, etc). The Cost Centers that are labeled in the master report are more detailed than i need (however, I will need that info in the future so I don't want to remove it). I created a table that has all of the Cost Centers with a Basic Label.

I added the Main Employee Table to the View then added the Cost Center Label table to the view. I connected the CostCenterNo from the Employee Table to the CostCenterNo in the Label table. I selected the Label name to be viewed instead of the Main Label from the employee table.

The problem is when I run the connection it duplicated several of the rows, going from 400+ to over 1200 rows. All I want to do is replace the complex label with a simple label for reporting.

Here is the code in the View. 

SELECT        dbo.EEMasterData.EmpNo, dbo.EEMasterData.EmpName,
dbo.EEMasterData.JobNo, dbo.EEMasterData.JobName, dbo.EEMasterData.OrgUnit,
dbo.EEMasterData.OrgName, dbo.EEMasterData.PArea,  
dbo.EEMasterData.PSubAreaNo,dbo.EEMasterData.PSubAreaName, 
dbo.EEMasterData.MasterCCNo,dbo.CostCenterConsolidatedLkUp.CostCenterMain, 
dbo.EEMasterData.PositionNo, dbo.EEMasterData.PositionName, 
dbo.EEMasterData.EmpGroupNo, dbo.EEMasterData.EmpGroupName, 
dbo.EEMasterData.EmpSubGroupNo, dbo.EEMasterData.EmpSubGroupName, 
dbo.EEMasterData.StartDate, dbo.EEMasterData.EndDate, dbo.EEMasterData.Status, 
dbo.EEMasterData.EmpStatus
FROM            dbo.CostCenterConsolidatedLkUp INNER JOIN
                     dbo.EEMasterData ON 
dbo.CostCenterConsolidatedLkUp.CostCenterMainNo = dbo.EEMasterData.MasterCCNo

I think the Join is what is causing the duplication but it wont let me change the join in the view designer.

Does anyone know how to change the join or is there a better way to add this label. There are approximately 100+ cost centers so coding would be tedious, this is why I am trying to do it through the view. (I'm on a short timetable, sorry).

Upvotes: 0

Views: 52

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This will give you the correct number of rows. It will give you a more or less random value for CostCenterMain from CostCenterConsolidatedLkUp.

You need sqlserver 2005+

SELECT e.EmpNo, e.EmpName,
e.JobNo, e.JobName, 
e.OrgUnit,
e.OrgName, 
e.PArea,  
e.PSubAreaNo,
e.PSubAreaName, 
e.MasterCCNo,
c.CostCenterMain, 
e.PositionNo, 
e.PositionName, 
e.EmpGroupNo, 
e.EmpGroupName, 
e.EmpSubGroupNo, 
e.EmpSubGroupName, 
e.StartDate, 
e.EndDate, 
e.Status, 
e.EmpStatus
FROM dbo.EEMasterData e 
cross apply
(select top 1 CostCenterMain
FROM dbo.CostCenterConsolidatedLkUp
WHERE CostCenterMainNo = e.MasterCCNo
) e

Upvotes: 1

Related Questions