Is there a SQL function to return rows only if row value is not found based on another condition

Question

Tried doing this a few times and not getting success. I have 2 tables orders and shipments. Order number is unique in the orders table but the value in the shipments table references order.number which can occur more than 1 time as an order can have multiple shipments.

So table orders has order.number and shipments has shipments.number, shipments.order_number and shipments.stock_location

I would like to only return the order.number where no shipments within that order.number were no shipments within that order shipped by a stock location. If I apply a where statement it just is removing the lines in the data tied to the shipment.number not taking into consideration a order.number could have a shipment where shipment.stock_location did ship from my excluded warehouse.

If it makes more sense here is actually my full code which is not working. What Im attempting to do is create a mailing list for any order where it fully drop shipped on all its shipments.

select orders.number, addresses.firstname,
       addresses.lastname, addresses.address1, addresses.address2,
       addresses.city, states.abbr,
       addresses.zipcode
from orders
join addresses on orders.ship_address_id = addresses.id
join shipments on shipments.order_id = orders.id
join states on states.id = addresses.state_id
where orders.id NOT IN (
    select shipments.order_id from shipments 
    where shipments.stock_location_id !=1 and orders.shipment_state='shipped') 
    AND orders.completed_at>= {{daterangepicker1.startFormattedString}} and
      orders.completed_at<= {{daterangepicker1.endFormattedString}} 
group by orders.number
order by orders.completed_at DESC;

I do not want any order numbers to show if any shipments.number within that order number shipped via stock location 1

Answer 1

You are a little bit unclear in your request, but I believe at a high level you want to return all the orders where there is no record in the shipped location for that order. The simplest pattern I can see to do this is as follows:

SELECT orders.number
FROM orders
         JOIN addresses ON orders.ship_address_id = addresses.id
         JOIN states ON states.id = addresses.state_id
WHERE orders.id NOT IN (
    SELECT shipments.order_id
    FROM shipments
    WHERE shipments.stock_location_id = 1
      AND orders.shipment_state = 'shipped'
    /* This part filters out all the order.ids with at least one shipment record from this location */
)
  AND orders.completed_at >= '{{daterangepicker1.startFormattedString}}'
  AND orders.completed_at <= '{{daterangepicker1.endFormattedString}}'
GROUP BY orders.id DESC;

This will return a list of all your orders except for ones with shipped information. Since the shipped information appears to be the only limiting caveat, this should be a clean way to address this problem.