Find The Subtasks That Did Not Execute Problem

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| task_id        | int     |
| subtasks_count | int     |
+----------------+---------+
task_id is the primary key for this table.
Each row in this table indicates that task_id was divided into subtasks_count subtasks labelled from 1 to subtasks_count.
It is guaranteed that 2 <= subtasks_count <= 20.
 

Table: Executed

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| task_id       | int     |
| subtask_id    | int     |
+---------------+---------+
(task_id, subtask_id) is the primary key for this table.
Each row in this table indicates that for the task task_id, the subtask with ID subtask_id was executed successfully.
It is guaranteed that subtask_id <= subtasks_count for each task_id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Tasks table:
+---------+----------------+
| task_id | subtasks_count |
+---------+----------------+
| 1       | 3              |
| 2       | 2              |
| 3       | 4              |
+---------+----------------+

Executed table:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 2          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |
| 3       | 4          |
+---------+------------+

Result table:
+---------+------------+
| task_id | subtask_id |
+---------+------------+
| 1       | 1          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
+---------+------------+
Task 1 was divided into 3 subtasks (1, 2, 3). Only subtask 2 was executed successfully, so we include (1, 1) and (1, 3) in the answer.
Task 2 was divided into 2 subtasks (1, 2). No subtask was executed successfully, so we include (2, 1) and (2, 2) in the answer.
Task 3 was divided into 4 subtasks (1, 2, 3, 4). All of the subtasks were executed successfully.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select t.task_id, x.subtask_id
from Tasks t
join (
    select 1 as subtask_id union select 2 as subtask_id union select 3 as subtask_id union
    select 4 as subtask_id union select 5 as subtask_id union select 6 as subtask_id union
    select 10 as subtask_id union select 11 as subtask_id union select 12 as subtask_id union
    select 7 as subtask_id union select 8 as subtask_id union select 9 as subtask_id union
    select 13 as subtask_id union select 14 as subtask_id union select 15 as subtask_id union
    select 16 as subtask_id union select 17 as subtask_id union select 18 as subtask_id union
    select 19 as subtask_id union select 20 as subtask_id
) x
where x.subtask_id <= t.subtasks_count and (t.task_id, x.subtask_id) not in (
    select task_id, subtask_id from Executed
)