Help with "anti" Join

[FlashMax]

I'm having a bit of trouble with conceptualizing this problem. I'm pretty new to mysql and I'm working on my first large-scale project.

Here's what I'm trying to understand. I have a table that keeps track of all orders. Then, each person can add one of those orders to their table. When that person queries the orders table, I don't want them to be able to see the ones that they have already put in their table. It's okay if someone else can see an order that Joe has taken, just not Joe.

Ex:
all_orders (order_ID, type, description)
1, plain, etc.
2, decorated, etc.
3, filled, etc.
4, outlined, etc.

joes_orders (order_ID, type, description, destination)
1, plain, etc., Charles Place
3, filled, etc., Apple Woods

When Joe queries all orders, I only want him to see:
2, decored, etc.
4, outlined, etc.

However, I can't seem to understand this "anti" join situation.

 

[r937]

you can use either a LEFT OUTER JOIN with a NULL test for unmatched rows, or a NOT EXISTS correlated subquery

but do you really have a separate table for each user???

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[FlashMax]

Well, this is not the actual layout or purpose of the tables, but it is a comparable example. For any possible future legal reason I'm not too comfortable giving the actual tables. However, for the purposes of my question, the example I gave is identical in theory to the actual situation.

Thank you for the help. I'll look into both of those solutions and see if I can get it to work.

 

[FlashMax]

Based on a little research, this is what I tried (but it didn't work):

SELECT *
FROM all_orders
OUTER WHERE NOT
EXISTS (

SELECT *
FROM joes_orders
INNER WHERE inner.order_ID = outer.order_ID
);

I'll keep trying different things, but if anyone could tell me where I'm going wrong, I would appreciate that.

 

[r937]

here are the two methods i referred to

SELECT all_orders.* 
  FROM all_orders
LEFT OUTER
  JOIN joes_orders
    ON joes_orders.order_ID = all_orders.order_ID 
 WHERE joes_orders.order_ID IS NULL

SELECT * 
  FROM all_orders
 WHERE NOT EXISTS 
       ( SELECT 1 
           FROM joes_orders
          WHERE order_ID = all_orders.order_ID )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[FlashMax]

Thank you so much for being patient with me. That worked perfectly. I'm not sure if one is preferable to the other as far as speed goes, but I think that I like the simplicity of the NOT EXISTS.

In my quest to learn SQL I will try to take as much from those two examples as I can.