CODEWITHSUNDEEP
sqlsql-server-2014recursive-query
Шыназ Алиш
Шыназ Алиш

Reputation: 399

hierarchical query in SQL

I have hierarchical table name dept_employees with structure below:

--------------------------------------
| id | parent_id | obj_id | obj_type |
--------------------------------------
| 1  |    null   |    34  |   dept   |
--------------------------------------
| 2  |       3   |    78  |   agent  |
--------------------------------------
| 3  |       1   |    78  | employee |
--------------------------------------
| 4  |       2   |   79   |   agent  |
--------------------------------------
| 5  |       3   |   80   |   agent  |
--------------------------------------
--------------------------------------
--------------------------------------
|1500|       2   |   934  | employee |
--------------------------------------
|1501|       4   |   935  | employee |
--------------------------------------

if obj_type = 'agent'

I need tak it from another table named Agents, Agents looks like:

--------------------------------------
| id |    name   |  phone |  Adress  |
--------------------------------------
| 1  |   Shynaz  |   123  |   Almaty |
--------------------------------------
| 2  |   Damir   |   589  |   Astana |
--------------------------------------
| 3  |   Oljas   |   637  | Karagand |
--------------------------------------
--------------------------------------
--------------------------------------
| 78 |   Erasyl  |   743  |   Aktau  |
--------------------------------------
| 79 |   Erok    |   743  |   Atyrau |
--------------------------------------

Or if obj_type = 'employee', then i need take it from another table named Employees. Employees looks like:

--------------------------------------
| id |    name   |  office |  salary |
--------------------------------------
| 1  |   Asyl    |   123  |   1000$  |
--------------------------------------
| 2  |   Zhandos |   589  |   1500$  |
--------------------------------------
--------------------------------------
--------------------------------------
| 78 |   Aleksei |   637  |    500$  |
--------------------------------------
--------------------------------------
--------------------------------------
|934|   Alibi   |   7    |    980$  |
--------------------------------------
|935|   Azat    |   8    |    980$  |
--------------------------------------

In first table parent_id is id in the same table, but in another record. For example: I get all child agents and employeers of record that id = 2. I need output like:

------------
id | name  |
------------
79 | Erok  |
------------
934| Alibi |
------------

And my query is:

WITH ChildNodes(id, name) 
AS(
SELECT 
   parent_de.id, 
   case parent_de.obj_type
    When 'agent' then parent_l.Name
    When 'employee' then parent_e.Fullname
   end as name
FROM FstP_Restored.dbo.dept_employees parent_de
left Join FstP_Restored.dbo.employees parent_e
    On parent_de.obj_id = parent_e.id --and parent_de.obj_type = 'employee'
left Join Agents_Restored.dbo.Лица parent_l 
    On parent_de.obj_id = parent_l.ID --and parent_de.obj_type = 'agent'
WHERE parent_de.id = 2--and parent_de.obj_type = isnull('agent', 'employees')

UNION ALL
SELECT
   child_de.id, 
   case child_de.obj_type
    When 'agent' then child_l.Name
    When 'employee' then child_e.Fullname
   end as name
FROM FstP_Restored.dbo.dept_employees child_de
 Inner Join FstP_Restored.dbo.employees child_e 
    On child_de.obj_id = child_e.id --and child_de.obj_type = 'employee' 
 Inner Join Agents_Restored.dbo.Лица child_l
    On child_de.obj_id = child_l.ID --and child_de.obj_type = 'agent'
 Inner JOIN ChildNodes parent ON parent.id = child_de.parent_id)

SELECT 
ChildNodes.id, 
ChildNodes.name
FROM ChildNodes
Where ChildNodes.id!= 2

With this query i got nothing. What i do wrong? SQL Server 2014

Upvotes: 0

Views: 156

Answers (1)

rabudde
rabudde

Reputation: 7722

Untested, but seems to be a simple thing, doesn't it? Can be extended for more object types.

SELECT
    t1.obj_id AS id,
    CASE t1.obj_type
        WHEN 'agent' THEN t2.name
        WHEN 'employee' THEN t3.name
        ELSE 'unknown'
    AS name
FROM dept_employees t1
LEFT JOIN Agents t2 ON t1.obj_id=t2.id
LEFT JOIN Employees t3 ON t1.obj_id=t3.id
WHERE t1.parent_id=2

Upvotes: 2

Related Questions