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

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".

CodePudding user response：

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 
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#3')
from   Risks_For_Task_3

CodePudding user response：

When using Union All you have to select all the columns that you want to insert. So in this case, TaskID is missing in your select statement.