How to get event headere value when event values have null

Transact-SQL
,
1

how to get event headere value when event values have null

Hi I have one doubt in sql server how to get event values if event values are null as per using self joins source table : product I need to check parentid with child id values exist or not if exist then get header event values when values have null or empty.
CREATE TABLE [dbo].[product](
[productid] varchar NULL,
[parentid] [int] NULL,
[childid] [int] NULL,
[event] varchar NULL

INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', NULL, 64, N'billing')
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 64, 65, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 65, 66, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 64, 67, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 67, 68, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 67, 69, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 67, 70, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 67, 71, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', NULL, 5, N'collect')
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 5, 6, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 6, 7, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 6, 8, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 5, 9, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 9, 10, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'1', 9, 11, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', NULL, 24, N'billing')
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', 24, 25, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', NULL, 101, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', NULL, 102, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', 25, 30, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', 101, 70, NULL)
GO
INSERT [dbo].[product] ([productid], [parentid], [childid], [event]) VALUES (N'2', 102, 80, NULL)
GO

based on above data i want output like below :

id | parentid |childid|event
1 | null |64 |billing
1 | 64 |65 | billing
1 | 65 |66 |NUll
1 | 64 |67 |billing
1 | 67 |68 |billing
1 | 67 |69 |billing
1 | 67 |70 |billing
1 | 67 |71 |billing

1 | null |05 |collect
1 | 05 |06 | collect
1 | 06 |07 |collect
1 | 06 |08 |collect
1 | 05 |09 |collect
1 | 09 |10 |collect
1 | 09 |11 |collect
2 | null |24 |billing
2 | 24 |25 | billing
2 | null |101 |billing
2 | NULL |102 billing
2 | 25 |32 |billing
2 | 101 |70 |billing
2 | 102 |80 |billing

i treid like below
selet prodcutid,isnull(p.parentid ,c.parentid)parentid
,isnull(p.childid,c.childid)childid,isnull(p.event,c.event)event
from product p join product c on p.parentid=c.childid
and p.id=c.id

I am unable to get expected result.could you please tell me how to write a query to achive this task in sql server

2

hi

does this help

don't understand what you are looking for !!!

select 
	p.[productid]
	, isnull(p.parentid 
	, c.parentid) parentid
    , isnull(p.childid
	, c.childid) childid
	, isnull(p.event,c.event) event
 from 
product p 
 join 
product c 
 on p.parentid=c.childid
and 
  p.[productid]  = c.productid
where 
  p.event IS NULL