SQL Self Join Status Winner query

Question

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)

Please, add a data sample of structure you have. What have you tried so far? — gofr1, Oct 01 '16 at 10:24
there is insufficient information provided to assist. Please provide additional sample data / schema's — Dheebs, Oct 01 '16 at 11:16
apologies added a schema and some sample data - just thought it was a simple enough schema it looked unnecessary to provide one — Paul, Oct 01 '16 at 11:26

LukStorms · Accepted Answer · 2016-10-01T13:40:53.487

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);

Thanks for you answer: I have attempted to answer my own queston...what would be wrong with the following statement ..select p.Customer_id ,p.Name_desc ,p.OrderIsAllowed_status as Parent_IsAllowed ,c.Customer_id ,c.ParentCustomer_id ,c.Name_desc ,c.OrderIsAllowed_status from Customer p inner join Customer c on p.customer_id = c.ParentCustomer_id where (p.OrderIsAllowed_status is null and c.OrderIsAllowed_status =1) or(p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status is null) or (p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status = 1) — Paul, Oct 01 '16 at 14:11
If the intention with that SQL is to get only those Children where [The child is allowed to order] is true, then that SQL isn't wrong. Although personally with a many-to-one relationship I'd put the "many" in the from and join the "one" to it. — LukStorms, Oct 01 '16 at 15:26

score 1 · Answer 2 · answered Oct 01 '16 at 14:04

You can use recursive CTE:

;WITH rec AS (
    SELECT  Customer_id,
            ParentCustomer_id,
            Name_desc,
            OrderIsAllowed_status,
            CAST(NULL AS bit) as parent_status,
            1 as [Level]
    FROM #Customer c
    WHERE ParentCustomer_id IS NULL
    UNION ALL
    SELECT  c.Customer_id,
            c.ParentCustomer_id,
            c.Name_desc,
            c.OrderIsAllowed_status,
            r.OrderIsAllowed_status,
            r.[Level]+ 1
    FROM rec r
    INNER JOIN #Customer c
        ON c.ParentCustomer_id = r.Customer_id
)

SELECT  r.Customer_id,
        r.ParentCustomer_id,
        r.Name_desc,
        r.OrderIsAllowed_status,
        rs.[Child is allowed to Order]
FROM rec r
INNER JOIN #rules rs
    ON COALESCE(r.[OrderIsAllowed_status],2) = COALESCE(rs.child_status,2)
        AND COALESCE(r.parent_status,2) = COALESCE(rs.parent_status,2)
WHERE r.ParentCustomer_id IS NOT NULL

Output:

Customer_id ParentCustomer_id   Name_desc           OrderIsAllowed_status   Child is allowed to Order
10          9                   Parent 3 - Child 1  NULL                    FALSE
11          9                   Parent 3 - Child 2  1                       FALSE
12          9                   Parent 3 - Child 3  0                       FALSE
6           5                   Parent 2 - Child 1  NULL                    FALSE
7           5                   Parent 2 - Child 2  1                       TRUE
8           5                   Parent 2 - Child 3  0                       FALSE
2           1                   Parent 1 - Child 1  NULL                    TRUE
3           1                   Parent 1 - Child 2  0                       FALSE
4           1                   Parent 1 - Child 3  1                       TRUE

I have used this tables:

CREATE TABLE #Customer (
    [Customer_id] [int] NOT NULL,
    [ParentCustomer_id] [int] NULL,
    [Name_desc] [nvarchar](50) NULL,
    [OrderIsAllowed_status] [bit] NULL
)

INSERT INTO #Customer VALUES
(1,null,'Parent 1',1),
(2,1,'Parent 1 - Child 1',null),
(3,1,'Parent 1 - Child 2',0),
(4,1,'Parent 1 - Child 3',1),
(5,null,'Parent 2',null),
(6,5,'Parent 2 - Child 1',null),
(7,5,'Parent 2 - Child 2',1),
(8,5,'Parent 2 - Child 3',0),
(9,null,'Parent 3',0),
(10,9,'Parent 3 - Child 1',null),
(11,9,'Parent 3 - Child 2',1),
(12,9,'Parent 3 - Child 3',0)

CREATE TABLE #rules (
    parent_status bit NULL,
    child_status bit NULL,
    [Child is allowed to Order] nvarchar(5) NULL
)

INSERT INTO #rules VALUES
(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')

score 0 · Answer 3 · answered Oct 01 '16 at 14:14

0

I have attempted to answer my own question...what would be wrong with the following ...if I was looking to return only the child customers that were allowed to place an order:

 select p.Customer_id
    ,p.Name_desc
    ,p.OrderIsAllowed_status as Parent_IsAllowed
    ,c.Customer_id
    ,c.ParentCustomer_id
    ,c.Name_desc
    ,c.OrderIsAllowed_status    
from Customer p
inner join Customer c
    on p.customer_id = c.ParentCustomer_id
where 
    (p.OrderIsAllowed_status is null and c.OrderIsAllowed_status =1)
    or(p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status is null)
    or (p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status = 1)

answered Oct 01 '16 at 14:14

Paul

1,103
1
13
18

1

This won't work if you have `grandparent - parent - child`. – gofr1 Oct 01 '16 at 14:17
Thanks - I missed this scenario..the historical usage of the table in my case only has one level of hierarchy - parent - child, but you are right I need to consider the original design – Paul Oct 01 '16 at 14:30
My pleasure! :) – gofr1 Oct 01 '16 at 15:07

SQL Self Join Status Winner query

3 Answers3