左联接产生值的行,另一个为空

首先,让我介绍一些上下文。假设我有一个状态的任务:完成或不完成。任务可以是不完整的,然后完成,或者立即完成。这些状态被保存。我想知道一个任务是否经历了那个不完整的状态。

/* Two possible states for a task, marked by a bit */
CREATE TABLE state (ID int unique not null, iscomplete bit not null);
INSERT INTO state (ID, iscomplete)
    VALUES (1, 0), (2, 1);

/* Three tasks, 2nd yet incomplete, 1 and 3 complete */
CREATE TABLE task (ID int unique not null, curr_state int not null);
INSERT INTO task (ID, curr_state)
    VALUES (1, 2), (2, 3), (3, 4);

/* All states for tasks, task 1 has had an incomplete state, 3 has not */
CREATE TABLE curr_state (ID int unique not null, task int not null, state int not null);
INSERT INTO curr_state (ID, task, state)
    VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1), (4, 3, 2);

STATE:              TASK:               CURR_STATE:
ID  | IsComplete    ID  | Curr_state    ID  | Task  | State
----+-----------    ----+-----------    ----+-------+-------
 1  | 0 (False)     1   |     2         1   |   1   |   1
 2  | 1 (True)      2   |     3         2   |   1   |   2
                    3   |     4         3   |   2   |   1
                                        4   |   3   |   2

通过这个查询,我几乎可以得到我想要的,这是所有具有不完整状态或 NULL 在里面 Through .

SELECT t.ID as Task, s.iscomplete as Complete, st.ID as Through

/* Get finished tasks */
FROM task t
JOIN curr_state c
ON t.curr_state = c.ID
JOIN state s
ON c.state = s.ID

/* JOIN to unfinished states */
LEFT JOIN curr_state cs
ON t.ID = cs.task
LEFT JOIN state st
ON cs.state = st.ID and st.iscomplete = 0

WHERE s.iscomplete = 1

-------- RESULT ---------

Task | Complete | Through
 1       true        1
 1       true      (null)
 3       true      (null)

-------- DESIRED --------

Task | Complete | Through
 1       true        1
 3       true      (null)

唯一的问题是,现在第一个任务有两次,首先是不完整状态,然后是 无效的 . 这怎么能避免呢?

玩弄我 this .

编辑:

第一个 LEFT JOIN 是冗余的,可以换成简单的 JOIN .