topic Re: hi all in Application Development and Automation Discussions

hi all

Former Member — Wed, 01 Aug 2007 14:24:50 GMT

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

Re: hi all

Former Member — Thu, 02 Aug 2007 08:30:01 GMT

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

Re: hi all

Former Member — Thu, 02 Aug 2007 08:41:56 GMT

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.

Re: hi all

Former Member — Thu, 02 Aug 2007 09:07:39 GMT

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...

SELF-JOIN

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

One More Example of SELF JOIN

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.