Finding pairs that do not exist in a different table

Question

I have a table (orders) with order id, location 1, location 2 and another table (mileage) with location 1 and location 2.

I'm using the Except action to return those location pairs in orders that are not in mileage. But I'm not sure how I can also return the corresponding order_id that belongs to those pairs (order_id doesn't exist in the mileage table). The only thing I can think of is having an outer select statement that searches orders for those location pairs. I haven't tried it but I'm looking for other options.

I have something like this.

SELECT location_id1, location_id2  
FROM orders 
except
SELECT lm.origin_id, lm.dest_id
from mileage

How can I also retrieve the order id for those pairs?

Answer 1

You might try using a Not Exists statement instead:

Select O.order_id, O.location_id1, O.location_id2
From orders As O
Where Not Exists    (
                    Select 1
                    From mileage As M1
                    Where M1.origin_id = O.location_id1
                        And M1.dest_id = O.location_id2
                    )

Another solution if you really wanted to use Except

Select O.order_id, O.location_id1, O.location_id2
From Orders As O
Except
Select O.order_id, O.location_id1, O.location_id2
From Orders As O
    Join Mileage As M
        On M.origin_id = O.location_id1
            And M.dest_id = O.location_id2