Reputation: 5083
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
Upvotes: 1
Views: 149
Reputation: 5083
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
Upvotes: 1
Reputation: 47464
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.
Upvotes: 2