You have used joins inconsistently .. listing the tables separated by commas and then using a WHERE clause is very old fashioned, confusing and not recommended. Use explicit JOINs with ON clauses.
One reason you are getting too much data is because you haven't included anything in the WHERE clause that indicates how those tables are to be joined (and hence why that approach is not recommended).
You have absolutely no need for that view as the details you need are already on ShiftDetail - joining to more tables than you need can often lead to multiple rows being returned when you only expected one.
Same applies to the self-join back to CompanyMaster (G) - why is that even there?
The simplified query now looks like this:
Select A.CompName,B.DeptNAme,C.NatOfWork,F.ShiftName,F.NoOfPersons
from #CompanyMaster A
inner join #ShiftMAster E ON A.CompID=E.companyID
inner Join #ShiftDetail F on F.ShiftID=E.ShiftID
LEFT OUTER join #NatOfWork C on C.DeptId=F.DeptID
LEFT OUTER JOIN #DepartmentMAster B ON B.DeptId = F.DeptID
Where A.CompID=1
It's much easier to see what is going on. However, if I run that query I'm still getting multiple rows. If I include
ORDER BY A.CompName,B.DeptNAme,C.NatOfWork,F.ShiftName,F.NoOfPersons
I get a clue (I used the data from your deleted comment) :
ABC Dept1 Work1 S1 5
ABC Dept1 Work1 S1 10
ABC Dept1 Work1 S2 6
ABC Dept1 Work1 S2 20
ABC Dept1 Work1 S3 7
ABC Dept1 Work1 S3 30
ABC Dept1 Work2 S1 5
ABC Dept1 Work2 S1 10
ABC Dept1 Work2 S2 6
ABC Dept1 Work2 S2 20
ABC Dept1 Work2 S3 7
ABC Dept1 Work2 S3 30
That first line Dept1, Work1, S1 5 people is wrong - the item with 5 people actually belongs to NatOfWork Work
2, so why did it come out as Work1?
The reason is that the joins need to be more specific, ShiftDetail includes a NatOfWork Id but we've ignored it up to now. Change the query to
Select A.CompName,B.DeptNAme,C.NatOfWork,F.ShiftName,F.NoOfPersons
from #CompanyMaster A
inner join #ShiftMAster E ON A.CompID=E.companyID
inner Join #ShiftDetail F on F.ShiftID=E.ShiftID
LEFT OUTER join #NatOfWork C on C.DeptId=F.DeptID AND C.NatWorkID = F.NAtOFIDLEFT OUTER JOIN #DepartmentMAster B ON B.DeptId = F.DeptID
Where A.CompID=1
ORDER BY A.CompName,B.DeptNAme,C.NatOfWork,F.ShiftName,F.NoOfPersons
and you will get the results you want.
You might find this CodeProject article useful
Visual Representation of SQL Joins[
^]