building a hierarchy from 3 tables

Question

I need to return a parent child relationship from 3 tables which are for bottom, mid and top level respectively. Easy stuff so far, and done already:

SELECT -1 ParentID, ID + 100000 ID,  txtName Value from tblLevel1
UNION
SELECT Level1ID + 100000 ParentID, ID + 50000 ID, txtName Value from tblLevel2
UNION
SELECT Level2ID + 50000 ParentID, ID ID, txtName Value from tblLevel3

My problem is that I need to add another field that describes the path of each entry.

For the first table, it is '-1;{id}).
For the second table is is '{Level1id};{id}'.
The third table's query should return '{Level1id};{Level2id};{id}'. The problem is the third one then.

The table structures are:

Level1; ID int; txtName varchar(50)

Level2; ID int; Level1ID int; txtName varchar(50)

Level3; ID int; Level2ID int; txtName varchar(50)

How do I get the full path in the query on the 3rd table (Level3)?

This is the current result

ParentID  ID       Value
-1       100001 Test company
-1       100006 Company A
-1       100007 Company B
-1       100008 The Primary Client
-1       100009 Overseas Client  1
100001  50001   Distribution
100006  50016   Attributes
100007  50018   Attributes
100008  50019   Development
100009  50029   Database
50001   1        Balance Sheet
50001   7        Cash Flow Statement
50001   10       Income Statement
50016   20       Vehicles
50016   21       Drivers
50019   33       Health
50029   29       Database

Answer 1

I would question your database design, as the separate table for each "level" seems like a bad idea to me. Since I don't know all of the specifics of your application though, I'll assume for the moment that it's how it has to be. Also, the whole business of adding numbers to the IDs looks to me like a BIG kludge that's going to bite you later.

Another, more immediate and constraining problem is that it looks like you want your third result set to have a different number of columns than the first two result sets. This is simply not possible with a UNION operator.

If you want to add another column to the first two queries then you should be able to get what you want in the third query simply by joining between the level 3 and level 2 tables to get the level 1 ID.

Answer 2

Solved it by joining to the level2 in the select on level 3:

SELECT -1 ParentID, ID + 100000 ID,  txtName Value, '-1;'+ cast(ID as varchar(10)) nodePath from tblLevel1
UNION
SELECT Level1ID + 100000 ParentID, ID + 50000 ID, txtName Value, '-1;'+ cast(Level1ID as varchar(10))+ ';'+ cast(ID as varchar(10)) nodePath from tblLevel2
UNION
SELECT Level2ID + 50000 ParentID, ID ID, txtName Value '-1;'+ cast(p.Level1ID as varchar(10))+ ';'+cast(m.Level2ID as varchar(10))+ ';'+cast(m.ID as varchar(10)) nodePath from tblLevel3
inner join tblLevel2 p on p.ID = m.Level2ID 

Results:

-1      100001  RRM Financial Master Templates  -1;1
-1      100006  Company A                       -1;6
-1      100007  Company B                       -1;7
-1      100008  The Primary Client              -1;8
-1      100009  Overseas Client 1               -1;9
100001  50001   Distribution                    -1;1;1
100006  50016   Attributes                      -1;6;16
100007  50018   Attributes                      -1;7;18
100008  50019   Development                     -1;8;19
100009  50029   Database                        -1;9;29
50001   1       Balance Sheet                   -1;1;1;1
50001   7       Cash Flow Statement             -1;1;1;7
50001   10      Income Statement                -1;1;1;10
50016   20      Vehicles                        -1;6;16;20
50016   21      Drivers                         -1;6;16;21
50019   33      Health                          -1;8;19;33
50029   29      Database                        -1;9;29;29