Get all parent rows and each row followed by their child’s rows

I’ve two tables one of them called Main-Level another one called Sub-level

Sub-level has a foreign key from the Main level (the relation between them Main-Level has one or Many Sub-levels )

what I want is to create a query to show the Main-level row followed by all Sub-level rows such as below screen-shot either by native SQL query or LINQ.

Update:

---

I used below but the problem is it the result such as Full OUTer JOIN !

select * from Sublevel
right join Mainlevel
on Sublevel.mainlevelID=Mainlevel.id 
order by coalesce(Sublevel.mainlevelID, Mainlevel.id),
         (case when Sublevel.mainlevelID is null then 1 else 0 end),Mainlevel.id;

Update 2:

---

Also, I tried below query but with no luck :

SELECT
    s.name,
    s.Id,
    CASE WHEN s.is_child = 1 THEN s.parentID END AS parent_id,
    m.name
FROM
    Mainlevel m
    INNER JOIN (
    SELECT id, name, parentID, 1 AS is_child
    FROM Sublevel
    UNION ALL
    SELECT id, name,Null, 0 AS is_child
    FROM Mainlevel
    ) s on m.id = s.mainlevelID
ORDER BY  m.id,is_child, s.mainlevelID 

My problem in simple language is How to make the child rows appeared below parent row

Answer

The overall plan is to have parent join (parent + child) order by (parent ID, child ID)

SELECT
    c.level_id,
    c.level_name,
    c.level_code,
    CASE WHEN c.is_child = 1 THEN c.parent_id END AS parent_id,
FROM
    mainLevel p
    INNER JOIN (
    SELECT level_id, level_name, level_code, parent_id, 1 AS is_child
    FROM subLevel
    UNION ALL
    SELECT level_id, level_name, level_code, level_id, 0 AS is_child
    FROM mainLevel
    ) c on p.level_id = c.parent_id
ORDER BY p.level_id, is_child, c.level_id

Additional version to adopt to the newly clarified column availability

SELECT
    w.name,
    w.id,
    CASE WHEN w.is_child = 1 THEN w.mid END AS parent_id
FROM
    Mainlevel m
    INNER JOIN (
    SELECT id, name, parentID AS mid, 1 AS is_child
    FROM Sublevel
    UNION ALL
    SELECT id, name, id AS mid, 0 AS is_child
    FROM Mainlevel
    ) w on m.id = w.mid
ORDER BY  m.id, is_child, w.id