I’ve been struggling with this problem.
I have 2 populated tables and a third empty one and I would like to populate the third with data from the other two. Correlate values
The idea is to assign every single ID from the 1st table to entries (several) found in the 2nd table that respect the condition where DestinedUserTypeID should be equal to UserTypeID),
Table 1
Id | UserName | UserTypeID |
---|---|---|
1 | Bla | 1 |
2 | Ble | 2 |
3 | Bli | 3 |
Table 2
ID | TaskName | DestinedUserTypeID |
---|---|---|
1 | Task A | 1 |
2 | Task B | 1 |
3 | Task C | 1 |
4 | Task D | 2 |
DESIRED TABLE, Table 3
ID | UserID | TaskID |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 1 | 3 |
…
Insert into Table3 ([UserId],[TaskID]) SELECT Id, (SELECT [Id] FROM [Table2] t2 WHERE [Id] <= 5 AND [DestinedUserTypeId] = 1) FROM t1 WHERE [UserTypeId] = 1
Thank you!
Advertisement
Answer
You can do it with a simple join linked to your table 1 and 2 and just insert into your table3
SELECT u.id, t.id, from table1 as u inner join table2 as t on u.userTypeId = t.DestinedUserTypeID