JOIN operator in SQL query is not returning intended result

Question

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';

natural join simply joins the same columns names between both tables in this case `d_ID` so `select e_name from emp natural join dep where dep.manager_id=emp.e_id;` should be the same as `select e_name from emp inner join dep on emp.d_ID = emp.d_ID.where dep.manager_id=emp.e_id;` — Raymond Nijland, Jan 20 '19 at 17:03
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Jan 20 '19 at 17:05
it depends on db & settings, but maybe the subtle difference between `d_id` and `d_ID` (case sensitivity) breaks the "natural join" (coz' this bases on column name)?? ...and then still the where clause is confusing/redundant — xerx593, Jan 20 '19 at 17:08
Your sample data are wrong. None of `d_ID` in table `emp` match the referenced column `d_ID` in table `dep`. — forpas, Jan 20 '19 at 17:10
@forpas is any problem with alter table emp add FOREIGN KEY(d_ID) references dep(d_ID); — Vishal choudhary, Jan 20 '19 at 17:18
@Vishalchoudhary The values 11, 12, 13, 14 of the column `d_ID` of table `emp` must reference the column `d_ID` of table `dep` whic are 1, 2, 3 but they don't since they don't match. — forpas, Jan 20 '19 at 17:21

score 1 · Accepted Answer · answered Jan 20 '19 at 18:25

1

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

answered Jan 20 '19 at 18:25

forpas

160,666
10
38
76

can you please exaplain what use of inner join in this code its show right reslut with out using inner (with join only ) – Vishal choudhary Jan 21 '19 at 16:25
@Vishalchoudhary join and inner join are the same thing. I tend to use inner join when I write code, but now you made me that in the above code I used once join and once inner join. Anyway. – forpas Jan 21 '19 at 16:28

score 0 · Answer 2 · answered Jan 20 '19 at 17:11

0

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;

answered Jan 20 '19 at 17:11

Gordon Linoff

1,242,037
58
646
786

JOIN operator in SQL query is not returning intended result

2 Answers2