SQL'不在'涉及三个表的查询

I have three tables, product, category and product_to_category. Product has the primary key product_id, category category_id and product_to_category p2c_id. Product_to_ category links products to categories in a many-to-many relationship using their respective ID's.

我有三个表，产品，类别和product_to_category。产品具有主键product_id，类别category_id和product_to_category p2c_id。 Product_to_ category使用各自的ID将产品链接到多对多关系中的类别。

Basically I want to write a query that would select all products from categories that do not exist in the category table. This is due to products being migrated across from another database.

基本上我想编写一个查询，从类别表中不存在的类别中选择所有产品。这是因为产品从另一个数据库迁移过来。

I had something like this but was a little lost.

我有类似的东西，但有点迷失。

SELECT * 
FROM product AS p 
LEFT JOIN product_to_category AS p2c ON p.product_id = p2c.product_id 
LEFT JOIN category AS c ON c.category_id

Basically that is as far as I have got. I need to join the category table to the product_to_category table where the product_to_category category_id is not in the category table. I may be completely on the wrong path but am stuck! Thanks in advance.

基本上这就是我所拥有的。我需要将category表加入product_to_category表，其中product_to_category category_id不在类别表中。我可能完全走错了路，但我被卡住了！提前致谢。

4 个解决方案

#1

Assumption: A product can be part of categories that exist, categories that do not exist, or no categories at all. You have not asked for products that belong to no categories at all, so the first LEFT JOIN from product to procduct_to_category should be an INNER JOIN.

假设：产品可以是存在的类别，不存在的类别或根本没有类别的一部分。您还没有要求产品属于任何类别，因此从产品到procduct_to_category的第一个LEFT JOIN应该是INNER JOIN。

Caveat: I am rusty at mysql so I am using SQL SERVER syntax. I forget if mysql has ON clauses or uses where clauses for JOINs. If ON clause is not supported, change them into WHERE clauses.

警告：我在mysql生锈，所以我使用的是SQL SERVER语法。我忘了如果mysql有ON子句或使用JOIN的where子句。如果不支持ON子句，请将它们更改为WHERE子句。

There are two common approaches: OUTER JOIN or a NOT IN clause (or a NOT EXISTS clause, which often behaves the same performance-wise as the NOT IN clause.)

有两种常见的方法：OUTER JOIN或NOT IN子句（或NOT EXISTS子句，它通常与NOT IN子句的性能相同。）

OUTER JOIN

外联

select p.*, p2c.category_id

选择p。*，p2c.category_id

from product p

来自产品p

INNER JOIN product_to_category p2c ON (p.product_id = p2c.product_id)

INNER JOIN product_to_category p2c ON（p.product_id = p2c.product_id）

LEFT JOIN category c ON p2c.category_id = c.category_id

LEFT JOIN类别c ON p2c.category_id = c.category_id

WHERE c.category_id IS NULL

WHERE c.category_id是NULL

The test for null will find the unmatched records.

null的测试将找到不匹配的记录。

NOT IN clause

NOT IN条款

SELECT p.*, p2c.category_id

SELECT p。*，p2c.category_id

FROM product p

从产品p

INNER JOIN product_to_category p2c ON (p.product_id = p2c.product_id)

INNER JOIN product_to_category p2c ON（p.product_id = p2c.product_id）

WHERE p2c.category_id NOT IN (SELECT c.category_id FROM category c)

WHERE p2c.category_id NOT IN（SELECT c.category_id FROM category c）

#2

If you're looking for products from nonexistent categories, I'd suggest

如果您正在寻找不存在的类别的产品，我建议

Select p.*,p2c.category_id
  from product p
  join product_to_category p2c
    on p.product_id=p2c.product_id
  left outer join category c
    on p2c.category_id=c.category_id
where c.category_id is null

#3

   SELECT p.*
     FROM product AS p
LEFT JOIN product_to_category AS p2c ON p.product_id = p2c.product_id
    WHERE NOT EXISTS (
          SELECT 1
            FROM category c
           WHERE c.category_id = p2c.category_id
    )

#4

SELECT * 
FROM product AS p 
JOIN product_to_category AS p2c ON p.product_id = p2c.product_id  
JOIN category AS c ON c.category_id != as.category.

Try this?

尝试这个？

#1