英文:
How to exclude row of Table1 if it appears in joined Table2?
问题
我有两个表连接，orders（id）和order_status（id，order_id，status_id）。
我想只返回那些没有给定status_id的订单，我尝试了以下方式：
SELECT order_id FROM orders
INNER JOIN order_status
ON orders.id = order_status.order_id
WHERE order_status.status_id != 1
但它不起作用，因为它仍然获取一些具有status_id = 2的订单，并且具有status_id = 2的给定订单与具有status_id = 1的相同，所以最终我仍然返回了status_id = 1的订单。
我想要的是，如果给定的订单具有order_status.status_id = 1，它只会忽略相同订单ID的所有其他行。
我该如何做到这一点？
英文:
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?
答案1
得分: 1
Use NOT EXISTS:
使用 `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
);
英文:
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
);
答案2
得分: 1
你可以使用 NOT EXISTS。例如：
select *
from orders o
where not exists (select 1 from order_status s
where s.order_id = o.id and s.status_id = 1)
英文:
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)
答案3
得分: 0
我会几乎口头使用它，不一定是最高效的。尚未测试，但思路是“从表中选择，在订单状态表中不存在状态ID不为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 IS NOT NULL)
注意：上述SQL查询已被翻译为中文。
英文:
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".
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
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)
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论