Select same column twice from the same table returning different values

Transact-SQL
2

Just fyi, it's much better to provide table structures rather than just a description of tables.


CREATE TABLE #employees ( EmpID int NOT NULL, LastName varchar(60) NOT NULL, FirstName varchar(50) NOT NULL, OrgID int NOT NULL )
INSERT INTO #employees VALUES (1, 'Gretzky', 'Douglas', 2);

CREATE TABLE #org_tree ( OrgID int NOT NULL, Name varchar(100) NOT NULL, Level smallint NOT NULL, ParentID int NOT NULL )
INSERT INTO #org_tree VALUES(1, 'Walmart', 1, 1),(2, 'Kitchen', 2, 1)

CREATE TABLE #org_emp ( OrgID int NOT NULL, EmpID int NOT NULL )
INSERT INTO #org_emp VALUES(2, 1)

Then here's a possible query:


SELECT e.FirstName, e.LastName, ot_parent.Name, ot.Name
FROM #org_emp oe
INNER JOIN #org_tree ot ON ot.OrgID = oe.OrgID
INNER JOIN #employees e ON e.EmpID = oe.EmpID
LEFT OUTER JOIN #org_tree ot_parent ON ot_parent.ParentID = ot.ParentID AND 
    ot_parent.Level = ot.Level - 1
1 Like