在所有孩子都符合条件的自我关系中选择行 [英] Select rows in a self-relationship where all children meet a condition

问题描述

我正在使用具有以下字段的单个表(称为documents):id，parent_id和status. parent_id字段引用同一表中的id字段. status字段的类型为ENUM('submitted', 'accepted', 'rejected').

I am working with a single table (called documents) with the following fields: id, parent_id, and status. The parent_id field refers to the id field in the same table. The status field is of type ENUM('submitted', 'accepted', 'rejected').

我想选择所有没有没有子级的documents，其中status = 'accepted'.

I would like to select all documents that have no children where status = 'accepted'.

我的第一次尝试是这样的:

My first attempt looked like this:

SELECT DISTINCT `documents`.*
FROM (`documents`)
LEFT OUTER JOIN `documents` children_documents
  ON `documents`.`id` = `children_documents`.`parent_id`
WHERE `children_documents`.`id` IS NULL
  OR `children_documents`.`status` != 'accepted'

问题是，仍将选择带有接受和不接受子代的文档.不应选择带有任何个被接受子级的文档.

The problem with this is that a document with both accepted and unaccepted children will still be selected. No document with any accepted children should be selected.

我觉得GROUP BY可能是我的朋友，但我不知道如何使用它来获得预期的结果.

I have a feeling GROUP BY might be my friend, but I can't figure out how I would use it to get the intended result.

推荐答案

SELECT DISTINCT `documents`.*
FROM (`documents`)
LEFT OUTER JOIN `documents` children_documents
  ON `documents`.`id` = `children_documents`.`parent_id`
  AND `children_documents`.`status` = 'accepted'
WHERE `children_documents`.`parent_id` IS NULL

这篇关于在所有孩子都符合条件的自我关系中选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！