SQLTeam.com | Weblogs | Forums

Perform a self join in a table


A table consists of three fields employeeid, company mail id, reporting inchargeid . i have to perform a self join in a table and find reporting incharge emaild of a particular employee.


Please show us sample data of the table(s) involved and expected output (from the sample data).
Also please describe which fields (and rules) you want to join, and perhaps a reason for joining.


As noted by bitsmed, you'd get more/better responses by including an actual table definition, sample data, and expected results.

As far as self joins go, it is not fundamentally different that joining to a different table, just make sure to alias the tables so you can reference each instance separately.

This example assumes the [reporting inchargeid] is an int field pointing to another employeeid in the same table.

	[employeeid] int NOT NULL, 
	[company mail id] int NOT NULL, 
	[reporting inchargeid] int

insert into @emp
SELECT 1,123, null
UNION SELECT 2, 321, 1
UNION SELECT 3, 251, null
UNION SELECT 4, 2201, 2
UNION SELECT 5, 4342, 2
UNION SELECT 6, 5256, 1

DECLARE @ParticularEmployeeID int;

SET @ParticularEmployeeID = 5
SELECT e.*, e2.[company mail id] as ReportingInChargeIDMailID
FROM @emp e
	ON e.[reporting inchargeid] = e2.employeeid
WHERE e.employeeid = @ParticularEmployeeID


Try this sample query to do this

SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  FROM [Order] JOIN Customer
    ON [Order].CustomerId = Customer.Id

Please go this one hoping you will get the help. http://www.w3resource.com/sql/joins/perform-a-self-join.php