Write a sql Query as per given scenarios

Question

Please help me to write this query. I faced this question in an interview.

tblEmployee

Emp_id	Emp_Name	Emp_Gender
1	John	Male
2	Dean	Male
3	Mary	Female

tblRelatives

Emp_id	Relatives_Id
1	2
1	3

Write a query to get the output given below.

Emp_id	Emp_Name	Father	Morther
1	John	Dean	Mary

Ananth G · Answer

Table1 select * from tblEmployee a ouput like this. Table2 select * from tblRelatives a output final the query output given below. select subquery.Emp_id, ( select b.Emp_Name from tblEmployee b where b.Emp_id= subquery.Emp_id) as Emp_Name, ( select c.Emp_Name from tblEmployee c left join tblRelatives d on c.Emp_id =d.Relatives_Id where d.Relatives= 'Father' and d.Emp_id=subquery.Emp_id ) as 'Father' , ( select c.Emp_Name from tblEmployee c left join tblRelatives d on c.Emp_id =d.Relatives_Id where d.Relatives= 'Mother' and d.Emp_id=subquery.Emp_id ) as 'Mother' from ( select distinct a.Emp_id from tblRelatives a group by a.Emp_id) subquery output

Satish Patel · Answer

Hi Amit This is the complete question asked by interviewer.

Amit Gupta · Answer

tblEmployee is having different emp_id in comparison with tblRelatives. How can you get the output like this? Is your question completed? Are you missing something in the question?

Midhun Tp · Answer

Hi, You will get the desired output with below query - select Emp_id,Emp_name,(select Emp_name from tblEmployee tb inner join tblRelatives tr on tb.Emp_id=tr.Relatives_id where tb.Emp_gender='Male') as father, (select Emp_name from tblEmployee tb inner join tblRelatives tr on tb.Emp_id=tr.Relatives_id where tb.Emp_gender='Female') as mother from tblEmployee where Emp_id in(Select distinct Emp_id from tblRelatives) But I'm not sure about what happens with more employees and more relations :)

Write a sql Query as per given scenarios

Answers (4)