I have a question about 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](50) NULL,
[parentid] [int] NULL,
[childid] [int] NULL,
[event] [varchar](50) NULL
)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', NULL, 64, N'billing')
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 64, 65, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 65, 66, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 64, 67, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 67, 68, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 67, 69, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 67, 70, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 67, 71, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', NULL, 5, N'collect')
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 5, 6, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 6, 7, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 6, 8, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 5, 9, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 9, 10, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'1', 9, 11, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', NULL, 24, N'billing')
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', 24, 25, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', NULL, 101, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', NULL, 102, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', 25, 30, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', 101, 70, NULL)
INSERT INTO [dbo].[product] ([productid], [parentid], [childid], [event])
VALUES (N'2', 102, 80, NULL)
Based on this data, I want output like this:
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 tried with this query:
SELECT
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 the expected results. Could you please tell me how to achieve this task in SQL Server?
