If there is data linked to an order number, how do i pull some but not the other (not exists?)?

I’m using Toad for Oracle.

I currently have Order Numbers with items in both our yard and our warehouse. I’m looking to see items that ONLY have items in the yard.

Essentially I need a count of the different items in the yard and a sum of their weight for each order

I currently have it where it’s counting the items for each order that are in the yard but it’s not limiting it to orders that ONLY have yard items

select ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, COUNT    (ORD.ITEM_NUMBER) AS LINE_COUNT, SUM(TIM.UNIT_WEIGHT * ORD.QTY) AS WEIGHT
from t_ORDER_DETAIL ORD
JOIN T_ITEM_MASTER TIM
ON ORD.WH_ID = TIM.WH_ID
AND ORD.ITEM_NUMBER = TIM.ITEM_NUMBER
JOIN SI_ORDER SIO
ON SIO.WH_ID = ORD.WH_ID
AND SIO.ORDER_NUMBER = ORD.ORDER_NUMBER
where ORD.wh_id = '603'
--AND ORDER_NUMBER = '1923421'
AND PROCESS_DATE >= SYSDATE -1
AND TIM.CLASS_ID IN ('GYARD','GSSPIPE','GBLKALLTHR','GCOPPPIPE','GCOPPCNC')
AND SIO.CARRIER = 'OT'
GROUP BY ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, WEIGHT
ORDER BY SIO.ORDER_NUMBER

table 1
order #   Item #
Order1         Y51
Order1         Y52
Order2         Y51
Order2         W35
Order3         W21
Order3         W80

Table 2
Item #       Location
Y51          Yard
Y52          Yard
W35          Warehouse
W21          Warehouse
W80          Warehouse

From the above sample set, I would only want to see Order1 because it is the only order that contains ONLY items located in the Yard

Answer

I am using your two sample tables (table 1 = orders, table 2 = items). This is how to find the order numbers of orders that contain yard items but no warehouse itms:

select o.orderno
from orders o
join items i on i.itemno = o.itemno
group by o.orderno
having count(case when i.location = 'Yard' then 1 end) > 0
   and count(case when i.location = 'Warehouse' then 1 end) = 0;

If you want to show complete orders, use above query in a subquery, e.g.:

select *
from orders
where orderno in ( <above query> )
order by orderno, itemno;

If you want an aggregation per order instead, you can modify the query accordingly, e.g.:

select
  o.orderno,
  count(*) as number_of_positions,
  sum(o.amount) as number_of_pieces,
  sum(o.amount * i.weight) as total_weight
from orders o
join items i on i.itemno = o.itemno
group by o.orderno
having count(case when i.location = 'Yard' then 1 end) > 0
   and count(case when i.location = 'Warehouse' then 1 end) = 0
order by o.orderno;

And if you want an aggregation per item, you might need the subquery plus a separate aggregation.