I have three tables.
Table1: Type Table
id |before_id | after_id |
+----+---------------------+
| 1 | 20 | 17|
Table2: History Table
+----+---------+----------+----------+-------------+--------
| id | user_id | start_at | end_at |
+----+---------+----------+----------+-------------+--------
| 1 | 2013 | 2022-11-07 00:21:30 | 2022-11-07 04:06:00 |
Table3: Mapping table
+----+------+
| id | type |
+----+------+
| 17 | 1 |
| 18 | 2 |
| 19 | 3 |
| 20 | 4 |
| 21 | 5 |
| 22 | 6 |
| 23 | 7 |
I wanted to join Table 1 and Table 2 on their id, and then update the Table1.before_id and Table1.after_id with the mappings shown in the Table 3. As shown in Table3, the before_id= 20 should be updated to 4, and after_id = 17 should be updated to 1.
IS it possible to do this in one single query? I was thinking of joining the Table1 and Table2 and then create a temporary table, and then update the values by referring to the Mapping table. How should I do that?
The final table should look like that
id |before_id | after_id | user_id | start_at | end_at
| 1 | 4 | 1 | 2013 | 2022-11-07 00:21:30 | 2022-11-07 04:06:00
PS: The tables except the mapping table are huge so I would also prefer if the solution is efficient
Thanks
Simply join the tables. The mapping table must be joined twice, because you want two different rows per type row. And when joining a table twice you must use table aliases, so as to tell one row from the other:
select
h.id,
mb.type as type_before,
ma.type as type_after,
h.user_id,
h.start_at,
h.end_at
from history h
join type t on t.id = h.id
join mapping mb on mb.id = t.before_id
join mapping ma on ma.id = t.after_id;