0

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!

2
  • Are you saying that you want to see all parents, including those with no children? That's a semi-join (e.g., LEFT JOIN). Simple queries are usually best handled on Stack Overflow; this site is meant for more specialized questions regarding database setup and configuration, optimization, and complex queries. Oct 22, 2018 at 16:47
  • I would like to see all parents, including those with no children (I'll add that edge case to the OP); however, I need a separate row for each parent, even if that parent's been joined, and it needs to be sorted so that the parent is like a header for the children I'll keep that in mind next time, I searched Stack Overflow first but I was finding more relevant similar questions here
    – chason
    Oct 22, 2018 at 17:25

1 Answer 1

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   |
6
  • I feel like you can avoid an unnecessary distinct sort operation by using UNION ALL if ParentID and ChildID are both unique. Oct 22, 2018 at 17:05
  • @AaronBertrand - I agree Oct 22, 2018 at 17:06
  • @chason - my example would definitely show parents without children. I updated my example to add an order by. Does that solve your issues? Oct 22, 2018 at 17:35
  • That almost gets it, but I need the parent-only row to show above the corresponding children, like a pseudo header row. When running your example, SQL spits out the the parents as the top two rows, as you'd expect from a simple union Also, as @Jon-of-All-Trades pointed out I need to return the parent even when no child exists, I'll add that to the OP I'm blaming Monday for my poorly explained question
    – chason
    Oct 22, 2018 at 17:39
  • @chason - can you update your question to show the problem - as it stands, my solution gives you what you asked for as expected results. Oct 22, 2018 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.