Question 223192 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to exclude row of Table1 if it appears in joined Table2?

I have two tables joined orders (id) and order_status (id, order_id, status_id).

I want to return only the orders that doesn’t have a given status_id, I’ve tried:

SELECT order_id FROM orders
INNER JOIN  order_status 
ON orders.id = order_status.order_id 
WHERE order_status.status_id != 1

But it doesn’t work because it still get some orders that have status_id = 2 and that given order that have status_id = 2 is the same that have status_id = 1, so in the end I’m still returning orders that are status_id = 1.

What I want is, if the given order have order_status.status_id = 1, it just ignore all the other rows of the same order id.

How can I do that?

Tags:

3

Answers


  1. 0

    Use NOT EXISTS:

    SELECT o.order_id 
FROM orders o
WHERE NOT EXISTS (
  SELECT *
  FROM order_status s
  WHERE s.order_id = o.id AND s.status_id = 1
);
    Login or Signup to reply.
  2. 0

    You can use NOT EXISTS. For example:

    select *
from orders o
where not exists (select 1 from order_status s
                  where s.order_id = o.id ans s.status_id = 1)
    Login or Signup to reply.
  3. 0

    I would use it verbally almost, not necessarily the most efficient. Not tested but the idea is "select from the tables where not exists a record on order_status where status_id is not null".

    SELECT 
  order_id 
FROM 
  orders
  LEFT JOIN order_status ON orders.id = order_status.order_id 
WHERE 
  NOT EXISTS (SELECT 1 FROM order_status WHERE order_id = orders.id AND status_id != null)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search