php - 选择多对多关系中没有类别的记录

Question

我有一个包含一些产品的数据库，它们可以用许多类别“标记”。有些产品没有指定的类别，我需要找到它们。我需要查找没有类别的记录。我创建了这个表:

项目

+----+------+
| ID | Item |
+----+------+
|  1 | qwe  |
|  2 | asw  |
|  3 | wgr  |
+----+------+

类别

+----+----------+
| ID | Category |
+----+----------+
|  1 | xxx      |
|  2 | yyy      |
+----+----------+

关系

+---------+--------+
| id_item | id_cat |
+---------+--------+
|       1 |      1 |
|       1 |      2 |
|       2 |      1 |
+---------+--------+

这是(不起作用)查询:

SELECT item.id COUNT(relationship.id_item) AS n
FROM item
JOIN relationships GROUP
BY relationships .n
WHERE item.id = relationships.id_item
AND relationships.n =0;

Answer 1

您可以使用带有 NOT IN 子句的子查询

SELECT i.id
FROM item i
WHERE i.id NOT IN ( SELECT r.id_item
    FROM RELATIONSHIP r);