Join three tables by one foreign key

Question

I have three tables:

Task (ID, TaskDescription)
Schedule (TaskID, ID, DueAt)
Audit (TaskID, TestID)

In Schedule table there is a list of scheduled tasks, and Audit table is for already done tasks. So first there is a row in Schedule, then when this task is done it's removing from Schedule table and added into Audit table.

Tasks table

+----+-----------------+
| ID | TaskDescription |
+----+-----------------+
|  1 | Clean room      |
|  2 | Remove trash    |
+----+-----------------+

Schedule table

+--------+--------+------------+
|   ID   | TaskID |   DueAt    |
+--------+--------+------------+
| 927847 |      1 | 2020-08-01 |
| 777777 |      2 | 2020-08-07 |
+--------+--------+------------+

Audit table

+--------+--------+
| TaskID | TestID |
+--------+--------+
|      1 |      3 |
|      1 |      2 |
|      1 |      1 |
|      2 |      4 |
+--------+--------+

I need to take all planned and already done tasks for one task ID. So for example, what I expect as result:

+---------+-----------------+-------------+----------------+--------+
| Task.ID | TaskDescription | Schedule.ID | Schedule.DueAt | TestID |
+---------+-----------------+-------------+----------------+--------+
|       1 | Clean room      | 927847      | 2020-08-01     | NULL   |
|       1 | Clean room      | NULL        | NULL           | 3      |
|       1 | Clean room      | NULL        | NULL           | 2      |
|       1 | Clean room      | NULL        | NULL           | 1      |
+---------+-----------------+-------------+----------------+--------+

That means already 3 tasks are done and one is scheduled for 2020-08-01. What i tried:

SELECT
    TaskID = t.ID,
    t.TaskDescription,
    ScheduleID = s.ID,
    ScheduleDueAt = s.DueAt,
    a.TestID
FROM Task t
    LEFT OUTER JOIN Schedule s
        ON (s.TaskID = t.ID)
    LEFT OUTER JOIN Audit a
        ON (a.TaskID = t.ID)
WHERE t.ID = '1'

But of course, I get the wrong result:

+---------+-----------------+-------------+----------------+--------+
| Task.ID | TaskDescription | Schedule.ID | Schedule.DueAt | TestID |
+---------+-----------------+-------------+----------------+--------+
|       1 | Clean room      |      927847 | 2020-08-01     |      3 |
|       1 | Clean room      |      927847 | 2020-08-01     |      2 |
|       1 | Clean room      |      927847 | 2020-08-01     |      1 |
+---------+-----------------+-------------+----------------+--------+

I'm going to use UNION for that but first wanted to ask maybe there is more right way how to do it.

user_2422776 · Answer

You need to union all the schedule and audit tables and query nulls for the missing columns. Then, you can join that result with the task table:

SELECT t.id, t.taskdescription, s.id, s.dueat, s.testid
FROM   task t
JOIN   (SELECT taskid, id, dueat, NULL AS testid
        FROM   schedule
        UNION ALL
        SELECT taskid, NULL, NULL, testid
        FROM audit) s ON t.id = s.taskid

user_7858451 · Answer

I agree that using UNION ALL as @Mureinik suggested is probably your best option here, but just for fun, another alternative would be this.

If you added another entry to your audit table for each taskID with a TestID of 0 (sort of as a default whenever a new task is created), then it will allow you to join onto the audit table, without the need for UNION.

So your Audit table would look like this:

+--------+--------+
| TaskID | TestID |
+--------+--------+
|      1 |      0 |
|      2 |      0 |
|      1 |      3 |
|      1 |      2 |
|      1 |      1 |
|      2 |      4 |
+--------+--------+

Then you can modify your query to join the schedule table as normal, but only where the audit table value is 0.

And finally, to keep it tidy, use NULLIF to hide the 0 for that TestID if you wish:

Select
    TaskID = t.ID,
    t.TaskDescription,
    ScheduleID = s.ID,
    ScheduleDueAt = s.DueAt,
    TestID= nullIF(a.TestID,0) 
from
    Task t
    inner join
        Audit a on 
            a.TaskID = t.ID
    left join 
        Schedule s on 
            s.TaskID = t.ID
            and a.TaskID = 0
where
    t.ID = 1

UPDATE: You will also need an additional where clause for when there is no scheduled task, to prevent an empty row returning:

where
    t.ID = 1
    and not (s.TaskID is null and a.TestID = 0)

Join three tables by one foreign key

2 Answers