sql - 当右表中有很多匹配的行时左连接

Question

我有两个表。

Product(id, name)
LineItem(id, product_id, order_id)
Order(id, state)

订单可以有很多产品。一个产品可以同时属于多个订单。

我想选择没有特定状态(即 1、2)订单的产品。

我的查询是

SELECT products.id, products.price
  FROM "products"
    LEFT OUTER JOIN line_items ON line_items.product_id = products.id
    LEFT OUTER JOIN orders ON orders.id = line_items.order_id AND orders.status IN (1, 2)
    WHERE (products.price > 0) AND (orders.id IS NULL) AND "products"."id" = $1
    GROUP BY products.id, products.price  [["id", 11]]

11 是产品的 id，它不应该出现在结果中，但它出现了。

Answer 1

我想选择没有特定状态(即 1、2)订单的产品。

SELECT * FROM products p    -- I would like to select Products
WHERE NOT EXISTS(           --  , which don't have
    SELECT *
    FROM orders o           -- orders 
    JOIN line_items li ON li.order_id = o.id
    WHERE li.product_id = p.id
    AND o.status IN (1,2) -- with specific statuses(i.e. 1, 2).
    );