sql - Incorrect Join in a View -

i creating view in sql manager 2012 has employee details (name, costcenter, etc). cost centers labeled in master report more detailed need (however, need info in future don't want remove it). created table has of cost centers basic label.

i added main employee table view added cost center label table view. connected costcenterno employee table costcenterno in label table. selected label name viewed instead of main label employee table.

the problem when run connection duplicated several of rows, going 400+ on 1200 rows. want replace complex label simple label reporting.

here code in 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            dbo.costcenterconsolidatedlkup inner join                      dbo.eemasterdata on  dbo.costcenterconsolidatedlkup.costcentermainno = dbo.eemasterdata.masterccno 

i think join causing duplication wont let me change join in view designer.

does know how change join or there better way add label. there approximately 100+ cost centers coding tedious, why trying through view. (i'm on short timetable, sorry).

this give correct number of rows. give more or less random value costcentermain 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 dbo.eemasterdata e  cross apply (select top 1 costcentermain dbo.costcenterconsolidatedlkup costcentermainno = e.masterccno ) e