Creating a hierarchy in SQL Server

Question

I have data in following format.

Table 1

e_id   e_name  e_type 
-------------------------
1      CBC     2
2      ABC     3
3      N2      1
4      CBC1    3
5      ABC1    3
6      N1      1

Table 2

N_ID   N_Name
---------------
3      N2
6      N1

Table 3

N_ID  E_ID
------------
3     1
3     2
3     3
6     4
6     5
6     6

And I want to build a hierarchy as follows.

e_id    e_name     e_type   n_id
------------------------------------
6       N1           1        6
4        - ABC1      3        6
5        - CBC1      3        6
3       N2           1        3
4        - ABC       3        3
5        - CBC       2        3

With Order by Nodes (ascending), child nodes (ascending).

I tried doing something similar to this,

SELECT u.e_id, 
CASE WHEN e_TYPE_ID = 1 THEN  u.e_name ELSE ' - ' + u.e_name END e_name, 
e_TYPE_ID, su.n_ID  
FROM table1 u
INNER JOIN table3 su on u.e_id = su.e_id 
WHERE EXISTS (SELECT N_ID FROM table2 WHERE N_ID = CASE WHEN u.e_TYPE_ID = 1 THEN u.e_id ELSE n_ID END)
ORDER BY  e_TYPE_ID, u.e_name,n_id

But I am not able to get the correct order, is there a better way to do this?

Answer 1

I will set you on the right path, you will need a CTE (common table expression) to accomplish this task:

;with EH as 
(
select 1 level, * from Table1 t1 where e_type = 1
union all
select level+1, * from EH 
join Table3 t3 on eh.e_id = t3.e_id 
join Table3 t32 on t3.n_id = t32.n_id and t3.e_id

--FUUUUUUUUUUUUUUUUUUUUU!!!!!!!!!!!!!!1111

[Update]

Why do you need those spaghetti tables anyway? Why don't you keep everything in one or two tables?

Answer 2

If you're using SQL Server 2008 (or 2008 R2), I would suggest using Hierarchy IDs as shown in Model Your Data Hierarchies With SQL Server 2008.

Answer 3

Use:

WITH summary AS (
  SELECT a.e_id,
         a.e_name,
         a.e_type,
         b.n_id,
         CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
    FROM TABLE1 a
    JOIN TABLE3 b ON b.e_id = a.e_id)
   SELECT s.e_id,
          CASE 
            WHEN s.rank > 1 THEN ' - '+ s.e_name 
            ELSE s.e_name 
          END AS e_name,
          s.e_type,
          s.n_id
     FROM summary s
 ORDER BY s.n_id DESC, s.rank, s.e_name

I tested using:

WITH table2 AS (
   SELECT 3 AS n_id, 'N2' AS n_name
   UNION ALL
   SELECT 6, 'N1'),
     table1 AS (
   SELECT 1 AS e_id, 'CBC' AS e_name, 2 AS e_type
   UNION ALL
   SELECT 2, 'ABC', 3
   UNION ALL
   SELECT 3, 'N2', 1
   UNION ALL
   SELECT 4, 'CBC1', 3
   UNION ALL
   SELECT 5, 'ABC1', 3
   UNION ALL
   SELECT 6, 'N1', 1),
     table3 AS (
   SELECT 3 AS n_id, 1 AS e_id
   UNION ALL
   SELECT 3, 2
   UNION ALL
   SELECT 3, 3
   UNION ALL
   SELECT 6, 4
   UNION ALL
   SELECT 6, 5
   UNION ALL
   SELECT 6, 6),
     summary AS (
   SELECT a.e_id,
          a.e_name,
          a.e_type,
          b.n_id,
          CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
     FROM TABLE1 a
     JOIN TABLE3 b ON b.e_id = a.e_id)
  SELECT s.e_id,
         CASE 
            WHEN s.rank > 1 THEN ' - '+ s.e_name 
            ELSE s.e_name 
         END AS e_name,
         s.e_type,
         s.n_id
    FROM summary s
ORDER BY s.n_id DESC, s.rank, s.e_name