Need help in Sql Query with Multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help in Sql Query with Multiple tables

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2010-03-23 : 16:09:09

Hi,
I have four tables
EmpHR : Empid EmpName
EmpPersonal: EmpId Nationality
EmpQualification: EmpId Qualification Remarks
EmpDependent: EmpId DependentName DependentSex

Relationships:
EmpHR to EmpPersonal(1 to 1) – (either 1 to 1 or 1 to zero)
EmpHR to EmpQualification( 1 to Many) (either 1 to zero or many)
EmpHR to EmpDependent(1 to Many) (either 1 to zero or many)

EmpId is the common column in all the four tables.

I require the output in the following way:
================================================
EmpId EmpName Nationality Qualification DependentName
1 Mohd Indian B.E Fatima
1 Mohd Indian M.B.A Abdul
2 Karan --- B.Com Rani
3 Kumar Indian B.C.A Prema
3 Kumar Indian M.C.A Preethi

=====================================
Query:

SELECT empHr.Empid, EmpHr.Empname, EmpPersonal.Nationality, EmpQualification.Qualification, EmpDependent.DependentName
FROM
((EmpHR LEFT JOIN EmpDependent ON EmpHR.EmpId=EmpDependent.EmpId) Left JOIN EmpPersonal ON EmpHR.EmpId=EmpPersonal.EmpId) INNER JOIN EmpQualification ON EmpHR.EmpId=EmpQualification.EmpId;

Output: My output is for every qualification the dependent name is printing number of times like cartesian product.

===============

Please help me, already i lost the hope of getting the required output.

Thanks & Regards

SSM...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-24 : 02:06:09

Try distinct in select list and let me know what is the output.

Vaibhav T

bhaskarvarada
Starting Member

8 Posts

Posted - 2010-03-24 : 02:41:45

Hi,

Since one EMPID can have many qualifications and multiple dependents , when u join EmpQualification and EmpDependent with only Empid The resultset will be a cartesian product

sureshmanian
Starting Member

26 Posts

Posted - 2010-04-03 : 09:32:31

Hi,
I got the answer with the help of my friend Trupti. Thanks. I would like to share the answer with you all.

create table EmpHR
(Empid int(4) PRIMARY KEY,
EmpName VARCHAR(50));

create table EmpPersonal
(Empid int(4) references EmpHR(Empid),
Nationality VARCHAR(50));

create table EmpQualification
(Empid int4 references EmpHR(Empid),
Qualification VARCHAR(50),
Remarks VARCHAR(500),
order_id int(4) default(1)
);
create table EmpDependent
(Empid int4 references EmpHR(Empid),
DependentName VARCHAR(50),
DependentSex VARCHAR(10),
order_id int(4) default(1)
);

insert into EmpHR
select 1 , 'Mohd'
union
select 2 , 'Karan'
union
select 3 , 'Kumar';

insert into EmpPersonal
select 1 , 'Indian'
union
select 3 , 'Indian';

insert into EmpQualification
select 1 , 'B.E','',1
union
select 1 , 'M.B.A','',2
union
select 2 , 'B.Com','',1
union
select 3 , 'B.C.A','',1
union
select 3 , 'M.C.A','',2;

insert into EmpDependent
select 1 , 'Fatima','F',1
union
select 1 , 'Abdul','M',2
union
select 2 , 'Rani','F',1
union
select 3 , 'Prema','M',1
union
select 3 , 'Preethi','F',2;

SQL Query :

SELECT empHr.Empid, EmpHr.Empname, case when EmpPersonal.Nationality is null then '--' else EmpPersonal.Nationality end ,
EmpQualification.Qualification, EmpDependent.DependentName
FROM EmpHR LEFT JOIN EmpPersonal on (EmpHR.EmpId=EmpPersonal.EmpId)
LEFT JOIN (select EmpId,order_id
from EmpDependent
union
select EmpId,order_id
from EmpQualification) eo on (EmpHR.EmpId=eo.EmpId )
LEFT JOIN EmpDependent ON (EmpHR.EmpId=EmpDependent.EmpId and eo.order_id=EmpDependent.order_id )
LEFT JOIN EmpQualification ON (EmpHR.EmpId=EmpQualification.EmpId and EmpQualification.order_id=eo.order_id);

Expected Output

1 Mohd Indian B.E Fatima
1 Mohd Indian M.B.A Abdul
2 Karan --- B.Com Rani
3 Kumar Indian B.C.A Prema
3 Kumar Indian M.C.A Preethi

Regards
SSM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources