2007 Aug 01 3:24 PM
hi all
could anybody give me how and when to use self join and equi join???
syntax + when to use self and when to use equi join???
thanx
rocky
2007 Aug 02 10:07 AM
Hi Rocky,,,,,
You hav got the defination of both joins from above answer ...
Here i am sending you some sample code with syntax...
It may clear your doubts...
<i><b>SELF-JOIN</b></i>
Self-Join is a concept where we are join a table to itself. This is useful when we want some particular information on join a table to itself. For eg.
EMPNO ENAME MGR
1 AA 2
2 BB Null
3 CC 1
In the above example we have a empno column and mgr column . In general they are the empno values only. Now if you have a query to find who is the manager for each then if i have to retrieve the data then we have to join the table to itself.
Here is the example for self join,
The Employee table is re is a table with structure as,
EmpId int
EmpName varchar(20)
Address varchar(50)
ManagerId int
Here ManagerId references some record in the Employee table itself.
Write a query to fetch the Name of the manager (employee), who manages an employee whose employee id is given.
Query:
Select m.EmpId ManagerId, m.EmpName ManagerName
From Employee e inner join Employee m on e.ManagerId = m.EmpId
Where e.EmpId = 1111
<i><b>One More Example of SELF JOIN</b></i>
Joining the same tbale to itself
use Pubs SELECT
authors.au_fname,
authors.au_lname,
authors1.au_fname AS Expr2,
authors1.au_lname AS Expr3
FROM
authors
INNER JOIN
authors authors1
ON authors.city
= authors1.city
AND authors.au_id
< authors1.au_id
WHERE
authors.city = 'Berkeley'
Plz Reward points if answer is useful...
Regards,
Mandeep.
2007 Aug 02 9:30 AM
self join--A join in which a table is joined with itself.
equijoin--A join statement that uses an equivalency operation. The converse of this is the nonequijoin operation.
Equi joins - contains only = sign
Non-Equi Joins - other simbles
regards
anju
2007 Aug 02 9:41 AM
Self join:
A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.
Eg: Select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.
Equi join:
Equi join is the join which contains an equal operator in its join condition.
2007 Aug 02 10:07 AM
Hi Rocky,,,,,
You hav got the defination of both joins from above answer ...
Here i am sending you some sample code with syntax...
It may clear your doubts...
<i><b>SELF-JOIN</b></i>
Self-Join is a concept where we are join a table to itself. This is useful when we want some particular information on join a table to itself. For eg.
EMPNO ENAME MGR
1 AA 2
2 BB Null
3 CC 1
In the above example we have a empno column and mgr column . In general they are the empno values only. Now if you have a query to find who is the manager for each then if i have to retrieve the data then we have to join the table to itself.
Here is the example for self join,
The Employee table is re is a table with structure as,
EmpId int
EmpName varchar(20)
Address varchar(50)
ManagerId int
Here ManagerId references some record in the Employee table itself.
Write a query to fetch the Name of the manager (employee), who manages an employee whose employee id is given.
Query:
Select m.EmpId ManagerId, m.EmpName ManagerName
From Employee e inner join Employee m on e.ManagerId = m.EmpId
Where e.EmpId = 1111
<i><b>One More Example of SELF JOIN</b></i>
Joining the same tbale to itself
use Pubs SELECT
authors.au_fname,
authors.au_lname,
authors1.au_fname AS Expr2,
authors1.au_lname AS Expr3
FROM
authors
INNER JOIN
authors authors1
ON authors.city
= authors1.city
AND authors.au_id
< authors1.au_id
WHERE
authors.city = 'Berkeley'
Plz Reward points if answer is useful...
Regards,
Mandeep.