Unanswered: SQL Union two tables with relational ordering/grouping
I have two tables with data that I need to get and display in a combobox.
What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.
This SQL seems to be more complex than I have done previously so I need some help.
I can get all of the records from both tables easily:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)
However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)
with CTE as
SELECT p.strName, p.ID as pID, 0 as cID, 'F' as Children
FROM tblParents as p
INNER JOIN tblChildren as c ON c.pID = p.ID
SELECT strName, pID, ID, 'T' as Children
select * from CTE
order by pID, Children, strName
That wasn't giving me the desired results either, so I ended up doing it like this:
DECLARE @Temp TABLE
/* Add rows with Parent information
pID | pName | '' | pID | pName
INSERT INTO @Temp
SELECT ID as ID, Name as pName, '' as cName, ID As pID, Name As aName FROM tblParent
/* Add rows with Child information
cID | '--->'| cName | pID | pName:cName
INSERT INTO @Temp
SELECT c.ID as ID, '--->' As pName, Name as cName, pID as pID, p.Name + ':' + c.Name As aName FROM tblChildren as c
INNER JOIN tblParent as p
ON pID = p.ID
/* Select the concatenation of pNAme and cNAme along with their unique IDs.
Results will be:
SELECT pName + cName As strName, ID as aID FROM @Temp
/* Order the results by the aName column.
Because all field data begins with the parent
name, the sorting will always have the children
listed under the parent.
ORDER BY aName