What is a self join? Explain it with an example?

Categories >> Software >> Databases >> DB Development
Suggest New Category

Question Posted / guest

16 Answers
60606 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is a self join? Explain it with an example?..

Answer / ramya

CREATE TABLE EMPLOYEE(

[EMPLOYEE_ID] INT PRIMARY KEY,

[NAME] NVARCHAR(50),

[MANAGER_ID] INT

)

INSERT INTO EMPLOYEE VALUES(101,'Mary',102)

INSERT INTO EMPLOYEE VALUES(102,'Ravi',NULL)

INSERT INTO EMPLOYEE VALUES(103,'Raj',102)

INSERT INTO EMPLOYEE VALUES(104,'Pete',103)

INSERT INTO EMPLOYEE VALUES(105,'Prasad',103)

INSERT INTO EMPLOYEE VALUES(106,'Ben',103)

select e1.employee_id,e1.name,e1.manager_id,e2.name from
employee e1 join employee e2
on e1.employee_id = e2.manager_id

Is This Answer Correct ?

1 Yes

0 No

What is a self join? Explain it with an example?..

Answer / mona

A table join to itself is known as self join.

select emp.ename,mngr.ename from emp.empl,emp.mngr where
emp.mngrno=mngr.empno

Is This Answer Correct ?

15 Yes

16 No

What is a self join? Explain it with an example?..

Answer / vijayan.t

A table join to itself is known as self join.

select e1.empid,e1.empname,e1.empmgrname where empmgrid =
empid;

here in a single table e1 having three fields(employee id,
employee name,manager id)one employee's manager id is
another one employee's employee id so compare that 2 ids and
we can get the particular employee's manager name...

Is This Answer Correct ?

4 Yes

5 No

What is a self join? Explain it with an example?..

Answer / pradeep sharma (http://www.tec

create table emp
(
empid int,
mgrid int,
empname varchar(50)
)
insert into emp values(1,2,'a')
insert into emp values(2,3,'b')
select * from emp

select e.empname as employee,d.empname as mgr from emp e
inner join emp d
on d.empid=e.mgrid

Is This Answer Correct ?

0 Yes

1 No

What is a self join? Explain it with an example?..

Answer / mike

You lost one Anju as Employee , because Anju does not have a
manager.
You should use left outer join in this case.

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 left outer join employees e2
on e1.emp_id=e2.emp_manager_id

Is This Answer Correct ?

0 Yes

2 No

What is a self join? Explain it with an example?..

Answer / nishant dubey

Table join itself using alias..
example
Select N,emp_id,N.emp_name,D.emp_id,D.emp_name from Nishant
N,Nishant D where N.emp_id=D.emp_id;

Is This Answer Correct ?

11 Yes

19 No

Post New Answer