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

Write the fastest query to find out how many rows exist in a table?

0 Answers  

On friday, you issued several insert statements using query analyzer. You then verified the data had been correctly entered with a select statement on monday, your users report that the data is not there what happened?

0 Answers  

What are defaults? Is there a column to which a default can't be bound?

2 Answers  

Write the difference between case version and decode version.

2 Answers  

Who are naive users?

0 Answers  

What is bit datatype and what's the information that can be stored inside a bit column?

2 Answers  

What is the system function to get the current user's user id?

4 Answers  

Explain the general syntax for a SELECT statements covering all the options.

3 Answers  

What is the difference between a primary index and a secondary index? What is a duplicate data entry in an index? Can a primary index contain duplicates?

5 Answers   IBM,

Can you have a nested transaction?

1 Answers  

How can you fix a poorly performing query?

0 Answers  

You have a table with three columns: amount1, amount2, and amount3 in a single row only one of these three columns will have a value and the other two will be null. Write a sql query to retrieve the values from these columns?

0 Answers