CODEWITHSUNDEEP
mysqlsqljoin
Vishal choudhary
Vishal choudhary

Reputation: 315

JOIN operator in SQL query is not returning intended result

I want to use JOIN operator to bring result here is my code: 

create table emp(E_id integer, e_name varchar(100), d_ID integer , primary key(e_id));
create table dep (d_ID integer, d_name varchar(100), manager_id integer ,primary key(d_ID) , FOREIGN KEY (manager_id) REFERENCES emp(E_id));
alter table emp add FOREIGN KEY(d_ID) references dep(d_ID);

insert  into dep values(11,'computer',1);
insert  into dep values(12,'commerce',2);
insert  into dep values(13,'technology',3);

insert into emp values(1,'vishal',11) ;
insert into emp values(2,'sachin',12) ;
insert into emp values(3,'deepal',13) ;
insert into emp values(4,'sumit',11) ;
insert into emp values(5,'vinay',11) ;
insert into emp values(6,'ravish',14) ;

I want to search manager of a particular employee with join operators like manager of vinay result should be vishal I used the nested query: 

  select e_name 
from emp 
where e_id in (select  manager_id
               from dep 
               where dep.d_id in(select d_ID 
                                       from emp 
                                       where emp.e_name ='sumit'));

I am getting the right result, but when I use JOIN operator it not working it show me result

vishal
sachin
deepal

i expcet result vishal here is code: 

select e_name 
from emp natural join dep 
where dep.manager_id=emp.e_id and emp.e_name='sumit';

Upvotes: 0

Views: 64

Answers (2)

forpas
forpas

Reputation: 164099

You need to join emp to dep and back to emp again:

select e.e_name 
from emp join dep 
on dep.d_id = emp.d_id
inner join emp e
on e.e_id = dep.manager_id
where emp.e_name='sumit';

See the demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Do not use natural join! It chooses the join conditions. You just want an inner join:

select e.e_name
from emp e inner join
     dep d
     on d.manager_id = e.e_id;

Upvotes: 0

Related Questions