What is a self join? Explain it with an example?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
How do you implement one-to-one, one-to-many and many-to- many relationships while designing tables?
1 Answers Flextronics, Hexaware, SQL Star,
Write short notes on XSU and JAVA
State the acid rules?
How long can an error message be in raiseerror function?
What are the constraints on severity level in raiseerror?
Explain about the storage and physical database design?
In which table collections errors are stored.
Explain the cube operator?
what is architecture of your datastage project??? i came across this question many times in interviews in specific what can i answer plz help me
You are testing the performance of a query the first time you run the query, the performance is slow. the second time you run the query, the performance is fast. why is this?
Hi , any one can help me on the same,this is regarding the Informatica Function doc,i want to know how developer is will develop the mapping. pl forwar the any function doc .just for my references. svlc75@yahoo.co.in
What are the types of database model?