SQL Order by parent and child

Question

Basically I need help in my query here. I want to be in right order which is child must be under parents name and in A-Z order. But if I add a subChild under child (Split 1) seem the order is wrong. It should be under Room Rose.

p/s : A subChild also can create another subChild

HERE I PROVIDE A DEMO

Appreciate your help me get this ordered correctly?

SELECT A.venueID
     , B.mainVenueID
     , A.venueName 
  FROM tblAdmVenue A 
  LEFT 
  JOIN tblAdmVenueLink B
    ON A.venueID = B.subVenueID
 ORDER   
    BY COALESCE(B.mainVenueID, A.venueID)
     , B.mainVenueID IS NOT NULL
     , A.venueID

I want it return an order something like this.

venueName
--------------
Banquet
Big Room
-Room Daisy
-Room Rose
  -Split 1
Hall
-Meeting Room WP

Seem this recursive approach also in not working

WITH venue_ctg AS (
  SELECT A.venueID, A.venueName, B.mainVenueID 
  FROM tblAdmVenue A LEFT JOIN tblAdmVenueLink B
  ON A.venueID = B.subVenueID
  WHERE B.mainVenueID IS NULL 

  UNION ALL

  SELECT A.venueID, A.venueName, B.mainVenueID 
  FROM tblAdmVenue A LEFT JOIN tblAdmVenueLink B
  ON A.venueID = B.subVenueID
  WHERE B.mainVenueID IS NOT NULL
)
SELECT *
FROM venue_ctg ORDER BY venueName

output given

Answer 1

For your data you can use this: To display this correctly, you can use a SEPARATPR like comma, and split the returned data, and check the hirarchy

-- schema
CREATE TABLE tblAdmVenue (
    venueID VARCHAR(225) NOT NULL,
    venueName VARCHAR(225) NOT NULL,
    PRIMARY KEY(venueID)
);

CREATE TABLE tblAdmVenueLink (
    venueLinkID VARCHAR(225) NOT NULL,
    mainVenueID VARCHAR(225) NOT NULL,
    subVenueID VARCHAR(225) NOT NULL,
    PRIMARY KEY(venueLinkID)
    -- FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);

-- data
INSERT INTO tblAdmVenue (venueID, venueName)
VALUES ('LA43', 'Big Room'), ('LA44', 'Hall'),
       ('LA45', 'Room Daisy'), ('LA46', 'Room Rose'),
       ('LA47', 'Banquet'), ('LA48', 'Split 1'),
       ('LA49', 'Meeting Room WP');

INSERT INTO tblAdmVenueLink (venueLinkID, mainVenueID, subVenueID)
VALUES ('1', 'LA43', 'LA45'), ('2', 'LA43', 'LA46'),
       ('3', 'LA46', 'LA48'), ('4', 'LA44', 'LA49');

✓

✓

✓

✓

with recursive cte (subVenueID, mainVenueID,level) as (
  select     subVenueID,
             mainVenueID, 1 as level
  from       tblAdmVenueLink
  union
  select     p.subVenueID,
             cte.mainVenueID,
             cte.level+1
  from       tblAdmVenueLink p
  inner join cte
          on p.mainVenueID = cte.subVenueID
)
select 
   
    CONCAT(GROUP_CONCAT(b.venueName  ORDER BY level DESC SEPARATOR  '-->') ,'-->',a.venueName)
from cte c 
LEFT JOIN tblAdmVenue a ON a.venueID = c.subVenueID 
LEFT JOIN tblAdmVenue b ON b.venueID = c.mainVenueID
GROUP BY subVenueID;

| CONCAT(GROUP_CONCAT(b.venueName  ORDER BY level DESC SEPARATOR  '-->') ,'-->',a.venueName) |
| :----------------------------------------------------------------------------------------- |
| Big Room-->Room Daisy                                                                      |
| Big Room-->Room Rose                                                                       |
| Big Room-->Room Rose-->Split 1                                                             |
| Hall-->Meeting Room WP                                                                     |

db<>fiddle here

Answer 2

Use only one table, not two. The first table has all the info needed.

Then start the CTE with the rows WHERE mainVenueID IS NULL, no JOIN needed.

This may be a good tutorial: https://stackoverflow.com/a/18660789/1766831

Its 'forest' is close to what you want.

Answer 3

I suppose you have:

1. table tblAdmVenue A is the venue list; and
2. table tblAdmVenueLink B is the tree relation table for parent-child

For your question on how to get a correct sorting order, I think one of the trick is to concatenate the parent venue names.

with q0(venueID, venueName, mainVenueID, venuePath) as (
  select
    A.venueID,
    A.venueName,
    null,
    A.venueName
  from tblAdmVenue A
       left join tblAdmVenue B on A.venueID = B.subVenueID
  where B.mainVenueID is null
  union all
  select
    A.venueID,
    A.venueName,
    q0.venueID,
    q0.venuePath + char(9) + A.venueName
  from q0
       inner join tblAdmVenue B on q0.venueID = B.mainVenueID
       inner join tblAdmVenue A on A.venueID = B.subVenueID
)
select venueID, venueName, mainVenueID
from q0
order by venuePath

Answer 4

You want your data ordered in alphabetical order and depth first.

A common solution for this is to traverse the structure from the top element, concatenating the path to each item as you go. You can then directly use the path for ordering.

Here is how to do it in MySQL 8.0 with a recursive query

with recursive cte(venueID, venueName, mainVenueID, path, depth) as (
    select v.venueID, v.venueName, cast(null as char(100)), venueName, 0
    from tblAdmVenue v
    where not exists (select 1 from tblAdmVenueLink l where l.subVenueID = v.venueID)
    union all
    select v.venueID, v.venueName, c.venueID, concat(c.path, '/', v.venueName), c.depth + 1
    from cte c
    inner join tblAdmVenueLink l on l.mainVenueID = c.venueID
    inner join tblAdmVenue v on v.venueID = l.subVenueID
)
select * from cte order by path

The anchor of the recursive query selects top nodes (ie rows whose ids do not exist in column subVenueID of the link table). Then, the recursive part follows the relations.

As a bonus, I added a level column that represents the depth of each node, starting at 0 for top nodes.

Demo on DB Fiddle:

venueID | venueName       | mainVenueID | path                       | depth
:------ | :-------------- | :---------- | :------------------------- | ----:
LA47    | Banquet         | null        | Banquet                    |     0
LA43    | Big Room        | null        | Big Room                   |     0
LA45    | Room Daisy      | LA43        | Big Room/Room Daisy        |     1
LA46    | Room Rose       | LA43        | Big Room/Room Rose         |     1
LA48    | Split 1         | LA46        | Big Room/Room Rose/Split 1 |     2
LA44    | Hall            | null        | Hall                       |     0
LA49    | Meeting Room WP | LA44        | Hall/Meeting Room WP       |     1