mysql - 如何检索依​​赖于同一表中其他行的所有行？

Question

所以我有这个表方案

|id| item_id | dependency_item_id | completed |

这个想法是让这个表中的行通过dependency_item_id -> item_id指向同一个表中的其他行。

我想要的是检索所有具有dependency_item_id值为NULL和completed值为 0 的记录

和

所有具有其父行的记录，该行是具有item_id = dependency_item_id, 状态completed = 1及其状态的行completed = 0

例子：

These are the records in the database.
|id|task_id|dependency_item_id|completed|
|1 |1      |null              |1        |
|2 |2      |null              |0        |
|3 |3      |1                 |0        |
|4 |4      |2                 |0        |
|5 |5      |2                 |0        |

With the query we should get only the second record
|id|task_id|dependency_item_id|completed|
|2 |2      |null              |0        |
|3 |3      |1                 |0        |

到目前为止，我的查询如下：

SELECT process.* 
    FROM tasks AS tasks 
        JOIN tasks AS dep ON dep.dependency_item_id = process.task_id 
    WHERE (dep.completed = 1 and tasks.completed = 0) OR tasks.dependency_item_id IS NULL

标签： mysqlsqldatabasejoin

Answer 1

我不得不调整您给我的查询以满足我的需要，因为使用给定的查询我无法检索具有空依赖项的记录。我的最终结果如下：

SELECT dep.* 
    FROM tasks AS task 
        LEFT JOIN tasks AS dep ON dep.dependency_item_id = task.item_id 
    WHERE (task.completed = 1 and dep.completed = 0) 
UNION 
SELECT * FROM tasks WHERE dependency_item_id IS NULL AND completed = 0