Select same column twice from the same table returning different values

Transact-SQL
1

I have three different tables that I´d like to join, and I´d like the same attribute twice.

Let´s say we have a table for employees containing EmpID, Lastname, Firstname and Organisation.
We have another table for the organisation tree containing OrgID, Name, Level and ParentID.
So, let´s say that we have one row in this table where OrgID = 1, Name = Walmart, Level =1 and ParentID = -1.
Then we have another row which looks like this: OrgID = 2, Name = Kitchen, Level = 2 and ParentId = 1. ParentID decides where in the organisation tree our organisation called Kitchen is. Since parentid = 1 and orgid = 1 for Walmart, the organisation Kitchen is under Walmart, Walmart\Kitchen.
The third table, org_emp, combines our employees with the organisation tree.
We have just two attributes, OrgID and EmpID.
So let´s say our first employee has EmpID = 1 and OrgID = 2. That means he is working in the department Kitchen under Walmart, since these two tables join on OrgID. Let´s say this employee has EmpID = 1, lastname = Gretzky, firstname Douglas and OrgID = 2.

The result I´d like one single sql query to make is:
Douglas, Gretzky, Walmart, Kitchen.

So in one row I´d like both the employees current organisation (Kitchen) and under which organisation Kitchen resides, that is Walmart.

The tabels would look like this.

Organisation:
OrgID | Name | Level | ParentID
1 | Walmart | 1 | -1
2 | Kitchen | 2 | 1

Employees:
EmpID | Lastname | Firstname | Organisation
1 | Gretzky | Douglas | 2

org_emp
OrgID | EmpID
2 | 1

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