Answer / ganesh

A join joins with itself is called self join
Working with self joins we use Alias tables

Answer / dhivya

lets take a table A which is joined to itself with its
alias is said to be self join.Most familiar example is
finding the manager of the employee..

select e1.emp_id manager_id,e2.emp_id employee_id from
employee e1,employee e2
where e1.emp_id=e2.emp_id

Answer / mangi

answer 1 is wrong

Answer / sanchit aggarwal(oracle dev.)

CREATE TABLE EMPLOYEE(

[EMPLOYEE_ID] INT PRIMARY KEY,

[NAME] NVARCHAR(50),

[MANAGER_ID] INT

)

GO

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.name from employee e1,employee e2
where e1.employee_id = e2.manager_id

Answer / lalithg

CREATE TABLE candytest
(kidId char(2),
candycolor varchar(10)
)
GO

INSERT INTO candytest SELECT 'K1', 'Yellow'
INSERT INTO candytest SELECT 'K1', 'Red'
INSERT INTO candytest SELECT 'K2', 'Red'
INSERT INTO candytest SELECT 'K2', 'Blue'
INSERT INTO candytest SELECT 'K3', 'White'
INSERT INTO candytest SELECT 'K3', 'Red'
INSERT INTO candytest SELECT 'K3', 'Yellow'
Go
--Now to find those kidid's which has both yellow and red
colored candies, we can write query as:

SELECT c1.kidid
FROM candytest AS c1 JOIN candytest AS c2
ON c1.candycolor = 'Red' AND c2.candycolor = 'Yellow'
AND c1.kidid = c2.kidid

Answer / madhavi

Joining the table to itself is self join.
Example:

To find the manager name for each employee in the employee
table:
select e1.empno employee_id,e1.ename employee_name,e2.ename
manager_name,e2.empno employee_num from emp e1,emp e2 where
e1.empno=e2.mgr;

Answer / krishna prasad a

For this Answer 1 is incorrect.

CREATE TABLE candytest
(kidId char(2),
candycolor varchar(10)
)
GO

INSERT INTO candytest SELECT 'K1', 'Yellow'
INSERT INTO candytest SELECT 'K1', 'Red'
INSERT INTO candytest SELECT 'K2', 'Red'
INSERT INTO candytest SELECT 'K2', 'Blue'
INSERT INTO candytest SELECT 'K3', 'White'
INSERT INTO candytest SELECT 'K3', 'Red'
INSERT INTO candytest SELECT 'K3', 'Yellow'
Go
--Now to find those kidid's which has both yellow and red
colored candies, we can write query as:

Select c1.candytest from
candytest c1,candytest c2
where c1.candycolor=c2.candycolor
and c1.kidid=c2.kidid

Answer / kannan

CREATE TABLE IF NOT EXISTS `emp` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) collate latin1_general_ci NOT NULL,
`dept_id` int(11) NOT NULL,
`mgr_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)


SELECT e2.name Employee_name, e1.name Manager_name
FROM emp e1, emp e2
WHERE e2.mgr_id = e1.id

Answer / anuj barthwal

A self join applies on one table only

Example:

emp_id emp_name emp_manager_id

1 Anuj NULL

2 Sunny 1

3 Suraj 1

4 Narban 2


if we want to get the name of the managers

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

manager employee

Anuj Sunny

Anuj Suraj

Sunny Narban


Self join means joining the single table to itself

Answer / sushant more (sybase dba)

CREATE TABLE EMPLOYEE(

[EMPLOYEE_ID] INT PRIMARY KEY,

[NAME] NVARCHAR(50),

[MANAGER_ID] INT

)

GO

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.NAME from EMPLOYEE e1,EMPLOYEE e2
where e1.EMPLOYEE_ID = e2.MANAGER_ID

If a column is an image value type, how you can compare column values? How can you use this column in join clause?

0 Answers  

Explain about the storage and physical database design?

0 Answers  

How to use timestamp datatypes

0 Answers  

agregator gives one to many records or many to one record

1 Answers   IBM,

fact table and dimension table containg one to many relationship or many to one relastionship

0 Answers  

What are the three basic rules which are to be followed for the relational model of the database?

0 Answers  

Explain about relational operator join?

0 Answers  

How can you fix a poorly performing query?

0 Answers  

Explain about network model?

0 Answers  

What is denormalization and when would you go for it?

9 Answers   NBP,

What is data modeling with example?

0 Answers  

What are data modelling techniques?

0 Answers