CODEWITHSUNDEEP
sqlsql-server-2008
Gabe
Gabe

Reputation: 6075

Finding pairs that do not exist in a different table

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?

Upvotes: 14

Views: 12352

Answers (5)

James Kingsbery
James Kingsbery

Reputation: 7486

MySQL doesn't support Except. For anyone who comes across this question using MySQL, here's how you do it:

http://nimal.info/blog/2007/intersection-and-set-difference-in-mysql-a-workaround-for-except/

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

I thought it was but as Gabe pointed, this does NOT work in SQL-Server 2008:

SELECT order_id
     , location_id1
     , location_id2  
FROM orders 
WHERE (location_id1, location_id2) NOT IN
  ( SELECT origin_id, dest_id
    FROM mileage
  )

Would this solution with EXCEPT (which actually is a JOIN between your original query and Orders) work fast or horribly? I have no idea.

SELECT o.order_id, o.location_id1, o.location_id2  
FROM orders o
  JOIN
    ( SELECT location_id1, location_id2  
      FROM orders 
      except
      SELECT origin_id, dest_id
      FROM mileage
    ) AS g
    ON o.location_id1 = g.location_id1
    AND o.location_id2 = g.location_id2

Upvotes: 2

Steve Mayne
Steve Mayne

Reputation: 22818

You could left-outer-join to the mileage table, and only return rows that don't join. Like so:

select O.order_id, O.location_id1, O.location_id2
from orders O left outer join mileage M1 on
  O.location_id1 = M1.origin_id and
  O.location_id2 = M1.dest_id
where M1.origin_id is NULL

Upvotes: 4

dcarneiro
dcarneiro

Reputation: 7170

If you want to get the pairs that do not exist on mileage table you can do something like

select location_id1, location_id2
from orders
where (select count(*) from mileage 
       where mileage.origin_id = location_id1 and mileage.dest_id = location_id2) = 0

Upvotes: 2

Thomas
Thomas

Reputation: 64645

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

Upvotes: 24

Related Questions