SQL query. URGENT.. HELP

[DKL01]

Hello All,

I have 2 tables

Table1:
EMP_ID LEVEL
1 100
2 200

Table2:
LEVEL LEVEL_NM PARENT_LEVEL
100 Programmer 200
200 State Manager 300
300 Global Manager 400
400 VP 400

I need to write a query which returns full hierarchy for particular EMP_ID.

For EMP_ID=1 I should get results
1 VP/Global Manager/State Manager/Programmer

For EMP_ID=2 I should get results
2 VP/Global Manager/State Manager

This is urgent. I really appreciate your help.

Thanks

 

[Mykanos]

Hi ,

Have you tried using the INNER JOIN syntax in SQL this will get you the result you are looking for.

 

[BlackKnight]

Try a stored procedure like this one. Modify as needbe.

Create Procedure "GetHierarchy"
@EmpID int
AS
SELECT Table2.[Level_NM], Table2.[Level] FROM (SELECT Tabe1.[Level] FROM Table1 WHERE (((Table1.[Emp_ID])=@EmpID))) as GetEmpLevel, Table2
WHERE (((Table2.[Level])>=[GetEmpLevel].[Level])) ORDER BY Table2.[Level] DESC