Self Join? and how to write self join for more than 3 tables

Sneha K

10y967

Hi i have learned about inner join, left join ,right join, outer join but i donno what is self join?. Any one explain me what is self join and it uses and also where to use.

Then also i want to know how to join more than three tables using self join.

Eg Customer Table

1)CustomerID uniqueidentifier primary key not null, 2)CustomerName Varchar(100), 3)CustomerTypeID uniqueidentifier null.

CustomerType

1)CustomerTypeID uniqueidentifier primary key not null 2)CustomerType varchar null

CustomerAddress Table

1)CustomerAddressID uniqueidentifier primary key not null 2)CustomerID uniqueidentifier null, 3)AddressID uniqueidentifier null,

Address

1)AddressID uniqueidentifier primary key not null, 2)Street varchar(100) null, 3)Location varchar(100) null, 4)Place varchar(100), null, 5)AreaID uniqueidentifier null, 6)PinCode .Varchar(100)

Area

1)AreaID uniqueidentifier primary key not null, 2)Area Varchar(100).

Here i mention some tables. Now i want to display CustomerName , CustomerType, Street,Place,Location, Area, PinCode. Now how i put self join for these tables . please any one explain this concept.

Advance Thanks...

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary10y ago
Hello! Sneha You use a self join when a table references data in itself. E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee. It's basically used where there is any relationship between rows stored in the same table.

But In your Case you are using 3 differents tables you can't use self join here. Instead of this you required inner or outer join............
Use Below Query for your desired result.

Select e.CustomerName,c.CustomerType,a.Street,a.Place,f.Area,a.Location,a.Pincode,
From Customer e
INNER JOIN
CustomerType c
On
e.CustomerTypeID =c.CustomerTypeID
INNER JOIN
CustomerAddress d
ON
e.CustomerID=d.CustomerID
INNER JOIN
Address a
e.AddressID=a.AddressID
INNER JOIN
Area f
ON
a.AreaID=f.AreaID

If this query is not working or in case not giving you desired result then you ca reply me....... I will check it
Vignesh Mani10y ago
One table join with the same table is called self join.

http://www.codeproject.com/Tips/712941/Types-of-Join-in-SQL-Server