join two tables, include one row from the parent table for each primary key

Question

I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I have two tables,

parents
ID | Name
0  | FOO
1  | BAR
2  | ASD

children
ID | Name | ParentID
10 | baz  | 0
11 | zap  | 0
12 | zam  | 1
13 | maz  | 1

I'd like to make a view that returns

ParentID | ChildID | Name | isChild
0        |         | FOO  | False
0        | 10      | baz  | True
0        | 11      | zap  | True
1        |         | BAR  | False
1        | 12      | zam  | True
1        | 13      | maz  | True
2        |         | ASD  | False

The data structure doesn't make much sense but it's populating 3rd party software and that's the only format option. Right now I'm hitting the database with an additional query for each parent. I imagine pulling it into one query would be more efficient, I've only got a few hundred parents

Thanks!

Answer 1

Perhaps I'm missing something, but I don't understand why this is not a simple UNION.

--Demo setup
Declare @Parent table (ID int, Name varchar(30))
insert into @Parent(id, Name) values(0,'FOO'),(1,'BAR'),(2,'ASD')
Declare @Children table (ID int, Name varchar(30),ParentId int)
insert into @Children(id, Name,ParentId) values(10,'bas',0),(11,'zap',0),(12,'zam',1),(13,'maz',1)

--The solution
;with UnionedData as
(
SELECT ID AS ParentId
    ,' ' AS ChildID
    ,NAME
    ,'False' AS isChild
FROM @Parent

UNION ALL

SELECT p.Id
    ,convert(VARCHAR(5), c.id) AS ChildID
    ,c.NAME
    ,'True' AS IsChild
FROM @Parent p
JOIN @Children c
    ON c.ParentId = p.id
)
SELECT *
FROM UnionedData
ORDER BY ParentId
    ,ChildID

---

| ParentId | ChildID | NAME | isChild |
|----------|---------|------|---------|
| 0        |         | FOO  | False   |
| 0        | 10      | bas  | True    |
| 0        | 11      | zap  | True    |
| 1        |         | BAR  | False   |
| 1        | 12      | zam  | True    |
| 1        | 13      | maz  | True    |
| 2        |         | ASD  | False   |