Consider the following tables in SQL Server:

Table Orders:

Table OrderItems:

In table OrderItems, Received and Scheduled can only have values 'N', 'X', or 'B'. I can't seem to figure out how to do the following:

select OrderNumber where Received != 'B' --if any of the Received values are 'B', then nothing should come back and Received = 'X' and Scheduled = 'B'.

Given the above table, OrderNumber should come back once in the result set. However, if 'B' appears in the Received column even once, then nothing should come back, because that fails the conditions.

So basically, I would like to get the OrderNumber back only if Received is either all 'X' or 'N' and 1 or more rows has Received = 'X' and Scheduled = 'B'.

I've tried using an all type clause with various conditions to do this, along with some other query ideas, but it doesn't take into account all rows in the final result:

select o.OrderNumber from Orders o 
inner join OrderItems oi on oi.OrderID = o.OrderID 
where o.OrderNumber = '1234' 
and oi.Received = all
    (select oi.Received from Orders o 
inner join OrderItems oi on oi.OrderID = o.OrderID 
where o.OrderNumber = '1234' 
and oi.Received in ('X', 'N') and oi.Received != 'B')

The above will give back all rows (so 5 OrderNumber results), even if I change row 4 to Received = 'B', for instance. The expected result for this should be:

Because the OrderItems table above has one row that matches the criteria requested (Received = 'X' and Scheduled = 'B') with no rows where Received = 'B'. If I changed row 4 to Received = 'B', then no results should come back.

SQL is not my strong suit, so any help here is much appreciated. I feel like a having clause or maybe a temp table would help, but this query will run over a large number of orders, and I worry about performance.

Thank you for your help!

Edited to make the query area look cleaner, fix the query, and explain the result

CodePudding user response：

You can use EXISTS and NOT EXISTS statements for this.

SELECT o.OrderNumber
FROM Orders o
WHERE o.OrderNumber = '1234'
      AND EXISTS
(
    SELECT *
    FROM OrderItems
    WHERE OrderNumber = o.OrderNumber
        AND Received = 'X'
        AND Scheduled = 'B'
)
      AND NOT EXISTS
(
    SELECT *
    FROM OrderItems
    WHERE OrderNumber = o.OrderNumber
          AND Received = 'B'
)

CodePudding user response：

If I understand correctly you can efficiently use a combination of exists and an aggregate given your 3 alphanumeric values:

select OrderNumber
from Orders o
where exists (
    select *
    from orderitems oi
    where oi.OrderId = o.OrderId
    group by orderid 
    having Min(received) > 'B'
);