Join Three tables in single resultset (For tree like structure)
-
I have three tables Category, SubParent & Master.. below are details. I am trying to populate the output like tree using single query. Don't know where to start. can anyone help?
Category and Master are related and SubParent and Master are related.
Category Table:
CategoryID
Name
Data:
1000ACategory
2200BCategory
SubParent Table:
SubParentID
Parent
Order
Text
Data:
101SubParent 1
211Pre Defined
312User Defined
401SubParent 2
541Pre Defined
642User Defined
Master Table:
MasterID
Text
SubParentID
CategoryID
Data:
100Option A21000
101Option B21000
102Option C31000
103Option D31000
104Option E52200
105Option F62200
106Option G52200
107Option H62200
Output:
ACategory
SubParent 1
Pre Defined
Option A
Option B
User Defined
Option C
Option D
BCategory
SubParent 2
Pre Defined
Option E
Option G
User Defined
Option F
Option H
I don't want the indention in the tree, but I want the Everything in the same result set to populate the tree in front-end.
-
WITH AllData(Category,SubParent1,SubParent2,Master) AS (
SELECT c.Name,s2.Text,s.Text,m.Text
FROM Master m
INNER JOIN SubParent s ON s.SubParentID=m.SubParentID
INNER JOIN SubParent s2 ON s2.SubParentID=s.Parent
INNER JOIN Category c ON c.CategoryID=m.CategoryID),
AllData2(Category,SubParent1,SubParent2,Master) AS (
SELECT Category,NULL,NULL,NULL
FROM AllData
UNION
SELECT Category,SubParent1,NULL,NULL
FROM AllData
UNION
SELECT Category,SubParent1,SubParent2,NULL
FROM AllData
UNION
SELECT Category,SubParent1,SubParent2,Master
FROM AllData)
SELECT COALESCE(Master,SubParent2,SubParent1,Category) AS Name
FROM AllData2
ORDER BY Category,SubParent1,SubParent2,Master
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks. You Rock!!
Question: If I want to include 'IDs' of the table(s). How to do that?
Also I want to pass CategoryID to the query? Sorry. I am really new to the query that you gave.
Sure: Surely I will keep posting rules in mind next time.
-
netpicker9 (10/1/2009)
Thanks. You Rock!!Question: If I want to include 'IDs' of the table(s). How to do that?
Also I want to pass CategoryID to the query? Sorry. I am really new to the query that you gave.
Sure: Surely I will keep posting rules in mind next time.
Not tested, but this should give you an idea
DECLARE @CategoryID INTSET @CategoryID=1000;
WITH AllData(Category,CategoryID,SubParent1,SubParentID1,SubParent2,SubParentID2,Master,MasterID) AS (
SELECT c.Name,c.CategoryID,s2.Text,s2.SubParentID,s.Text,s.SubParentID,m.Text,m.MasterID
FROM Master m
INNER JOIN SubParent s ON s.SubParentID=m.SubParentID
INNER JOIN SubParent s2 ON s2.SubParentID=s.Parent
INNER JOIN Category c ON c.CategoryID=m.CategoryID
WHERE m.CategoryID=@CategoryID),
AllData2(Category,CategoryID,SubParent1,SubParentID1,SubParent2,SubParentID2,Master,MasterID) AS (
SELECT Category,CategoryID,NULL,NULL,NULL,NULL,NULL,NULL
FROM AllData
UNION
SELECT Category,CategoryID,SubParent1,SubParentID1,NULL,NULL,NULL,NULL
FROM AllData
UNION
SELECT Category,CategoryID,SubParent1,SubParentID1,SubParent2,SubParentID2,NULL,NULL
FROM AllData
UNION
SELECT Category,CategoryID,SubParent1,SubParentID1,SubParent2,SubParentID2,Master,MasterID
FROM AllData)
SELECT COALESCE(Master,SubParent2,SubParent1,Category) AS Name,
CategoryID,
SubParentID1,
SubParentID2,
MasterID
FROM AllData2
ORDER BY Category,SubParent1,SubParent2,Master
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply