I have a customer table that has a self join (Parent - Child), I need to write a query that returns the child customer/s where the Status Allows the parent or child to place an order. The column is a bit column and is nullable.
The results returned would be based on the following matrix:
parent_status child_status Child is allowed to Order
null null FALSE
null 0 FALSE
null 1 TRUE
1 null TRUE
1 1 TRUE
1 0 FALSE
0 null FALSE
0 1 FALSE
0 0 FALSE
as requested here is schema and script for data
CREATE TABLE [dbo].[Customer](
[Customer_id] [int] NOT NULL,
[ParentCustomer_id] [int] NULL,
[Name_desc] [nvarchar](50) NULL,
[OrderIsAllowed_status] [bit] NULL)
GO
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(1,null,'Parent 1',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(2,1,'Parent 1 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(3,1,'Parent 1 - Child 2',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(4,1,'Parent 1 - Child 3',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(5,null,'Parent 2',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(6,5,'Parent 2 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(7,5,'Parent 2 - Child 2',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(8,5,'Parent 2 - Child 3',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(9,null,'Parent 3',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(10,9,'Parent 3 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(11,9,'Parent 3 - Child 2',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(12,9,'Parent 3 - Child 3',0)
Based on the truth table, the CASE WHEN for it would be something like below.
The example uses a table variable just for demonstration.
declare @Customer table (Customer_id int NOT NULL, ParentCustomer_id int, OrderIsAllowed_status bit);
insert @Customer ([Customer_id], [ParentCustomer_id], [OrderIsAllowed_status]) values
(1,null,1),
(2,1,null),
(3,1,0),
(4,1,1),
(5,null,null),
(6,5,null),
(7,5,1),
(8,5,0),
(9,null,0),
(10,9,null),
(11,9,1),
(12,9,0);
select
child.Customer_id,
child.ParentCustomer_id,
(case
when child.ParentCustomer_id is null then 'Parent '+ cast(child.Customer_id as varchar)
else concat('Parent ',parent.Customer_id,' - Child ',child.Customer_id)
end) as Name_desc,
parent.OrderIsAllowed_status as parent_status,
child.OrderIsAllowed_status as child_status,
cast(case
when child.OrderIsAllowed_status = 1 and parent.OrderIsAllowed_status = 1 then 1
when child.OrderIsAllowed_status = 1 and parent.OrderIsAllowed_status is null then 1
when child.OrderIsAllowed_status is null and parent.OrderIsAllowed_status = 1 then 1
else 0
end as bit) as [Child is allowed to Order]
from @Customer child
left join @Customer parent on (child.ParentCustomer_id = parent.Customer_id);