SQLTeam.com | Weblogs | Forums

Perform a self join in a table


#1

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.


#2

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.


#3

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.

DECLARE @emp 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
LEFT OUTER JOIN @emp e2
	ON e.[reporting inchargeid] = e2.employeeid
WHERE e.employeeid = @ParticularEmployeeID

#4

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