CODEWITHSUNDEEP
sql-servert-sql
Matt
Matt

Reputation: 3

SQL Join that only returns records that are missing in other table

Consider two tables in a SQL Server database:

Order

OrderId | OrderType | etc...

Shipment

ShipmentId | OrderId | ShipType | etc..

I'm trying to write a select statement that returns OrderIds :

where Order.OrderType = 'EXCHANGE'

and there is no corresponding record in the Shipment table that has 

Shipment.OrderId = Order.OrderId and Shipment.ShipType = 'BOX'

The reason for this is that depending on the OrderType, a BOX may or may not to be shipped to the customer. I want to grab all the OrderIds that are of OrderType = 'EXCHANGE' but have no corresponding BOX shipment so they can be forwarded to be shipped.

Upvotes: 0

Views: 54

Answers (2)

Marko Radivojević
Marko Radivojević

Reputation: 448

You could use LEFT JOIN.

SELECT
    o.*
FROM Order o
    LEFT JOIN Shipment s ON s.OrderId = o.OrderId AND s.ShipType = 'BOX'
WHERE
    o.OrderType = 'EXCHANGE'
    AND s.ShipmentId IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use not exists:

select o.*
from order o
where o.OrderType = 'EXCHANGE' and
      not exists (select 1
                  from shipment s
                  where s.OrderId = o.OrderId and s.ShipType = 'BOX'
                 );

Note: As written this won't work because order is a SQL keyword and reserved word -- that makes it a very poor choice of table name (you can escape the name, although you do not in the question). I would suggest you call it something else, such as orders.

Upvotes: 2

Related Questions