Write a sql Query as per given scenarios

Satish Patel

10y1.2k

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

tblEmployee

tblRelatives

Write a query to get the output given below.

Emp_id	Emp_Name	Father	Morther
1	John	Dean	Mary

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Accepted 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
0
Satish Patel10y ago
Hi Amit
This is the complete question asked by interviewer.
+1
Midhun Tp10y ago
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 :)
0
Amit Gupta10y ago
tblEmployee is having different emp_id in comparison withtblRelatives. How can you get the output like this? Is your question completed? Are you missing something in the question?
0