In SQL how do you join these tables into another one?

November 11, 2022

Sorry I’m struggling with something that should be simple.

I have table "Risks_For_Task_1":

+--------------+-------------+--------------+
| RiskName     | Mitigation  | RiskLevel    |
+--------------+-------------+--------------+
| Risk A       | Mitigate#1  | Medium       |
| Risk B       | Mitigate#2  | Low          |
| Risk C       | Mitigate#3  | High         |
+--------------+-------------+--------------+

And a table "Risks_For_Task_2":

+--------------+-------------+--------------+
| RiskName     | Mitigation  | RiskLevel    |
+--------------+-------------+--------------+
| Risk D       | Mitigate#4  | Low          |
| Risk E       | Mitigate#5  | Low          |
| Risk F       | Mitigate#6  | Medium       |
+--------------+-------------+--------------+

And a table "Risks_For_Task_3":

+--------------+-------------+--------------+
| RiskName     | Mitigation  | RiskLevel    |
+--------------+-------------+--------------+
| Risk G       | Mitigate#7  | Medium       |
| Risk H       | Mitigate#8  | High         |
| Risk I       | Mitigate#9  | Medium       |
+--------------+-------------+--------------+

And a table "Tasks":

+--------------+-------------+
| ID           | TaskName    |
+--------------+-------------+
| 1            | Task#1      |
| 2            | Task#2      |
| 3            | Task#3      |
+--------------+-------------+

I wish to combine Risks_For_Task_1, Risks_For_Task_2, and Risks_For_Task_3, and put them into an existing table called "Task_Risks" with an extra column referencing the ID from the table Tasks. So the result should look like this:

+--------------+-------------+--------------+--------------+
| RiskName     | Mitigation  | RiskLevel    | TaskID       |
+--------------+-------------+--------------+--------------+
| Risk A       | Mitigate#1  | Medium       | 1            |
| Risk B       | Mitigate#2  | Low          | 1            |
| Risk C       | Mitigate#3  | High         | 1            |
| Risk D       | Mitigate#4  | Low          | 2            |
| Risk E       | Mitigate#5  | Low          | 2            |
| Risk F       | Mitigate#6  | Medium       | 2            |
| Risk G       | Mitigate#7  | Medium       | 3            |
| Risk H       | Mitigate#8  | High         | 3            |
| Risk I       | Mitigate#9  | Medium       | 3            |
+--------------+-------------+--------------+--------------+

This is what I wrote:

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
Select RiskName, Mitigation, RiskLevel, TaskID from 
    ((Select RiskName, Mitigation, RiskLevel from Risks_For_Task_1 
        Full Join 
    Select ID from Tasks where TaskName='Task#1')
    Union All
    (Select RiskName, Mitigation, RiskLevel from Risks_For_Task_2 
        Full Join 
    Select ID from Tasks where TaskName='Task#2')
    Union All
    (Select RiskName, Mitigation, RiskLevel from Risks_For_Task_3 
        Full Join 
    Select ID from Tasks where TaskName='Task#3'));

Above code gives the error "invalid table name".

>Solution :

You should not store the risks in separate tables; instead you should have one table with all the risks and an additional task column with a referential constraint to the primary key of the tasks table.

However, that appears to be what you have in your Task_Risks table.

Use a sub-qery:

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#1')
from   Risks_For_Task_1 
Union All
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#2')
from   Risks_For_Task_2 
UNION ALL
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#3')
from   Risks_For_Task_3

or a join:

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
SELECT r.RiskName,
       r.Mitigation,
       r.RiskLevel,
       t.id
FROM   (
         Select RiskName, Mitigation, RiskLevel, 'Task#1' AS task
         from   Risks_For_Task_1 
       UNION ALL
         Select RiskName, Mitigation, RiskLevel, 'Task#2'
         from   Risks_For_Task_2 
       UNION ALL
         Select RiskName, Mitigation, RiskLevel, 'Task#3'
         from   Risks_For_Task_3
       ) r
       INNER JOIN tasks t
       ON (r.task = t.task)