Given these two tables:
Table1
| id1 | id2 |
|---|---|
| 224 | 2341 |
| 5234 | 9394 |
| 6243 | 2342 |
| ⋮ | ⋮ |
Table2
| id | value |
|---|---|
| 224 | 2 |
| 2341 | 52 |
| 2342 | 24 |
| 5234 | 23 |
| 6243 | 242 |
| 9394 | 99 |
| ⋮ | ⋮ |
I would like to create this table:
Table3
| id1 | id2 | diff |
|---|---|---|
| 224 | 2341 | 52-2 |
| 5234 | 9394 | 99-23 |
| 6243 | 2342 | 24-242 |
| ⋮ | ⋮ | ⋮ |
where diff = (value of id2) - (value of id1).
I tried doing this with multiple nested sub-queries with joins, but it seemed quite a belabored/kludgy way of doing it. What is the simplest way of doing this?
You need to join table2 twice
SELECT x.id, y.id, y.value - x.value
FROM table1 t1
JOIN table2 x
ON t1.id1 = x.id
JOIN table2 y
ON t1.id2 = y.id
Regarding the comment you got "Two aliased joins", you temporarily rename the tables so they can be distinguished. In my example, I used:
JOIN table2 x
which means that this instance of table2 can be referred to as x. You can use "AS" for clarity if you wish:
JOIN table2 AS x
I typically use aliases for all tables (even when not necessary) and qualify all attributes with the aliases. It makes it easier to understand where the attribute origins from.
