SQL join/merge two hierarchyid tables

Question

Is there any way to join/merge two hierarchical tables in SQL Server?

I have two sample tables: BOM and ComponentDetail

declare @BOM table 
    (
      BomNode hierarchyid primary key,
      ComponentID int
    )

insert into @BOM 
values
    ('/',             NULL),
    ('/1/',           1),
    ('/1/1/',         2),
    ('/1/2/',         3),
    ('/1/3/',         4)

declare @ComponentDetail table
   (
       CompNode hierarchyid primary key,
       ComponentID int,
       SteelProductID int
   )

insert into @ComponentDetail 
values
   ('/',          NULL,NULL),
   ('/1/',        2, NULL),
   ('/1/1/',      2,1),
   ('/1/2/',      2,2)

What I want to do is to combine those two tables temporarily just to view the result in my application:

UPDATE: @Sean Lange I've made a mistake in declaring Result table - it should look like the one below:

insert into @Result 
    values 
       ('/',     NULL, NULL),
       ('/1/',   1, NULL),
       ('/1/1/',   2, NULL),
       ('/1/1/1/', NULL, 1),
       ('/1/1/2/', NULL, 2),
       ('/1/2/',   3, NULL),
       ('/1/3/',   4, NULL)

select 
    Node.ToString() as NodeChar, ComponentID, SteelProductID 
from @Result

Here is a diagram of desired output: Output diagram

Anyone ?

Answer 1

You can join those two tables together just like any other tables. In this case a full outer join is probably what you want.

This returns your desired output from your sample data:

select Node = coalesce(b.BomNode.ToString(), c.CompNode.ToString())
    , ComponentID = coalesce(b.ComponentID, c.ComponentID)
    , c.SteelProductID
from @BOM b
full outer join @ComponentDetail c on c.CompNode = b.BomNode

--EDIT--

With my new understanding I think it is something like this (but it isn't quite right on the hierarchy stuff but not clear to me what you want there):

select distinct Node = coalesce(b.BomNode.ToString(), c.CompNode.ToString()) + coalesce(convert(varchar(4), c.ComponentID) + '/', '')
    , ComponentID = coalesce(b.ComponentID, c.ComponentID)
    , c.SteelProductID
from @BOM b
full outer join @ComponentDetail c on b.ComponentID = c.ComponentID