By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- 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');
Records: 7 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
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
| 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 |