Exclude those orders which has service code 'AAAA'

An order can have multiple services. As per the functionality, I have to remove those orders from the selection which have a service code 'AAAA'. I mean if an order has two services code 'AAAA' and 'AAAB'. Then this must not be included in the output because this order has a Service code 'AAAA'. Please see the screenshot below for more info.

Without any condition Image

Query:

Select BO.OrderID ,OrderDate ,BackgroundServiceID , BST.BST_Name ,BST_Code from 
BackgroundOrder BO 
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID 
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID

Expected Output Image

Expected Output Query:

Select BO.OrderID ,OrderDate ,BackgroundServiceID , BST.BST_Name ,BST_Code from 
BackgroundOrder BO 
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID 
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID
Where BST.BST_Code = 'AAAB' AND BO.OrderID not in 
(Select BO.OrderID from 
BackgroundOrder BO 
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID 
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID
Where BST.BST_Code = 'AAAA'
)

Please suggest some other ways to get the above output without using a not-in statement.

1 answer

  • answered 2021-06-19 10:10 Stu

    You can wrap your query in a cte and count the occurences of 'AAAA' per OrderId then filter where it's zero

    with t as (
        select BO.OrderID, OrderDate, BackgroundServiceID, BST.BST_Name, BST_Code,
            Sum(case when bst_code='AAAA' then 1 else 0 end) over(partition by BO.OrderID) cnt=0
        from BackgroundOrder BO 
        join BackgroundOrderService BOS on BOS.OrderID = BO.OrderID 
        join ams.lkpBkgSvcType BST on BST.BST_ID = BOS.BackgroundServiceID
    )
    select OrderID, OrderDate, BackgroundServiceID, BST_Name, BST_Code
    from t
    where cnt=0