Click here to Skip to main content
14,424,734 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have an 3 Tables and one View Table They are
1)CompanyMaster(CompanyID,Com.Name)
2)DepartmentMaster(Depart.ID,Depart.Name)
3)NatureOFWork(NatOFWorkID.ID,Depart.ID,NatOfWork)
4)ShiftMAster(ShiftMAsterID,CompanyID)
4)ShiftDetail(ShiftMAsterID,Depart.ID,NatOFWorkID,ShiftName,NoOfPerson)
View Table is
1)ShiftNo(Id,ShiftName)
Id ShifTName
1 S1
2 S2
3 S3
Now My question is need to join these Tables and Views To get the Below Answer format

Com.Name Dept.Name Nat.Work ShiftName No.Persons
ABc Dept1 Work1 S1 10
ABC Dept1 Work1 S2 20
ABC Dept1 Work1 S3 30

What I have tried:

I tried the Below Query

Select A.CompName,B.DepartName,C.NAtureOfWork,D.ShiftName,F.NoOfPersons
from tblCompanyMaster A,vwShiftName D,tblDepartMaster B
join tblNatOfWorkDetail C on C.DeptId=B.DeptID,
tblShiftMaster E
inner join tblCompanyMaster G on G.CompID=E.companyID
Left Outer Join tblShiftDetail F on F.ShiftID=E.ShiftID
Where A.CompID=1

But it Shows
Com.Name Dept.Name Nat.Work ShiftName No.Persons
ABc       Dept1     Work1   S1        10
ABC       Dept1     Work1   S2        10
ABC       Dept1     Work1   S3        10
ABc       Dept1     Work1   S1        20
ABC       Dept1     Work1   S2        20
ABC       Dept1     Work1   S3        20
ABc       Dept1     Work1   S1        30
ABC       Dept1     Work1   S2        30
ABC       Dept1     Work1   S3        30


Please Help me..
Posted
Updated 30-Jan-19 4:20am
Comments
CHill60 30-Jan-19 6:50am
   
We'll need sample data for all of the tables. You've clearly got multiple items per id on one of them
Santosh kumar Pithani 30-Jan-19 7:29am
   
Your view "ShiftNo" has (3 rows) so its returning records as M*N ( View rows * Each records of table).There is no relation Between view and other tables so give proper relation.
RmcbainTheThird 30-Jan-19 7:43am
   
along with sample data a schema would help
Flower@12 30-Jan-19 7:46am
   
1)CompanyMaster(CompanyID numeric,Com.Name nvarchar)
2)DepartmentMaster(Depart.ID numeric,Depart.Name nvarchar)
3)NatureOFWork(NatOFWorkID.ID numeric,Depart.ID numeric,NatOfWork nvarchar)
4)ShiftMAster(ShiftMAsterID numeric,CompanyID numeric)
4)ShiftDetail(ShiftMAsterID numeric,Depart.ID numeric,NatOFWorkID numeric,ShiftName nvarchar,NoOfPerson numeric
ZurdoDev 30-Jan-19 8:03am
   
Looks like you might need to group. Or you might need to fix a join. We don't know what your data is so I'm not sure what you want us to do.
Rate this:
Please Sign up or sign in to vote.

Solution 1

--Its a estimated query it will help for you..
SELECT 
 A.CompName 
,B.DepartName 
,C.NAtureOfWork 
,F.ShiftName
,F.NoOfPersons
FROM 
 ShiftMAster AS E
INNER JOIN CompanyMaster AS  A 
                         ON (A.companyID=E.companyID)
INNER JOIN ShiftDetail AS F 
                         ON (F.ShiftMAsterID=E.ShiftMAsterID) 
INNER JOIN DepartmentMaster AS B 
                         ON (B.DepartID=F.DepartID)
INNER JOIN NatOfWorkDetail AS C 
                         ON (C.NatOFWorkIDID=F.NatOFWorkIDID
						        AND C.DepartID=F.DepartID
							 )
     
	 WHERE A.CompID=1 
	        --AND EXISTS(select 1 FROM ShiftNo AS D WHERE  D.ShifTName=F.ShiftName)
   
Comments
CHill60 30-Jan-19 9:04am
   
Doesn't produce the results that the OP wanted though My apologies, I got that wrong
Santosh kumar Pithani 30-Jan-19 23:32pm
   
No one expects apologies from Masters.We don't know which query is right or wrong its a OP fault i.e i have given all inner joins.Otherwise my answer would be different with right joins
CHill60 31-Jan-19 3:40am
   
"No one expects apologies from Masters" .. I always try to admit when I'm wrong. We're all on this learning journey together :-)
Rate this:
Please Sign up or sign in to vote.

Solution 2

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 Work2, 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[^]
   
Comments
Flower@12 31-Jan-19 2:27am
   
Thank u sir
CHill60 31-Jan-19 3:39am
   
My pleasure!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100