sql - 选择自连接关系中没有 child 的 parent

Question

我有一个简单的类别名称表，它通过 category_parent = id 与自身相关。我需要选择没有子项的记录。

我从其他答案中拼凑了这个声明:

SELECT cat.category_name AS 'Child Name', cat.id AS 'Child ID',
cat.category_parent AS 'Childs Parent', cat_par.category_name AS 'Parent Name', 
cat_par.id AS 'Parent ID'
FROM category AS cat
LEFT JOIN category AS cat_par ON cat.category_parent = cat_par.id
WHERE cat_par.id IS NULL;

这将成功选择没有 PARENT 的记录。我尝试将最后一个子句更改为 WHERE cat.category_parent IS NULL 但结果还是空的。

我也试过这个基于另一个答案的声明:

SELECT cat.category_name AS 'Child Name', cat.id AS 'Child ID', 
cat.category_parent AS 'Childs Parent' cat_par.category_name AS 'Parent Name', 
cat_par.id AS 'Parent ID'
FROM category AS cat
WHERE NOT EXISTS 
(SELECT id FROM category AS cat_par WHERE cat.category_parent = cat_par.id);

返回错误 No value given for an or more required parameters.

Answer 1

Select p.id, p.category_name
From category As p
Where Not Exists (
  Select 'x' 
  From Category c 
  Where c.category_parent = p.id
)