Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    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:
    Code:
    		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)

    Any advice would be greatly appreciated.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    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
    
        UNION 
        
        SELECT strName, pID, ID, 'T' as Children
        FROM tblChildren
    )
    
    select * from CTE
    order by pID, Children, strName
    Hope this helps.

  3. #3
    Join Date
    Feb 2009
    Posts
    33
    That wasn't giving me the desired results either, so I ended up doing it like this:
    Code:
    DECLARE @Temp TABLE 
    ( 
    	ID uniqueidentifier, 
    	pName varchar(100),
    	cName varchar(100),
    	pID uniqueidentifier,
    	aName varchar(200)
    );
    /* 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:
       ParentName1
       --->ChildName1
       --->ChildName2
       ParenntName2
       --->ChildName1
       --->ChildName2
       --->ChildName3
       ParentName4
       ParentName5
    */
    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
    
    RETURN
    Do you see any flaws in this logic?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •