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!

  • 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. – Jon of All Trades Oct 22 '18 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 '18 at 17:25
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   |
  • I feel like you can avoid an unnecessary distinct sort operation by using UNION ALL if ParentID and ChildID are both unique. – Aaron Bertrand Oct 22 '18 at 17:05
  • @AaronBertrand - I agree – Scott Hodgin Oct 22 '18 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? – Scott Hodgin Oct 22 '18 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 '18 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. – Scott Hodgin Oct 22 '18 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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