I have the following tables:
Table1:
| id | name | model |
|---|---|---|
| 2 | 2 | ar |
| 1 | 1 | st |
Table2:
| id | name | model2 |
|---|---|---|
| 2 | 2 | th |
| 1 | 1 | st |
Table_keys:
| model | model number |
|---|---|
| ar | 127 |
| st | 234 |
| th | 338 |
And trying to create one table as following:
Table_result:
| id | name | model | model2 | model number_1 | model number_2 |
|---|---|---|---|---|---|
| 2 | 2 | ar | th | 127 | 338 |
How can I join the two tables with the keys table twice? I used left outer join but then "model number 1" can be matched with either model1 or model2, I need to use "model number" again, in a new column but for model2
CodePudding user response：
You can join on table_keys twice, with two different aliases:
SELECT t1.id, t1.name, t1.model, t2.model2,
tk1.model_number AS model_number_1,
tk2.model_number AS model_number_2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id AND t1.name = t2.name
LEFT JOIN table_keys tk1 ON t1.model = tk1.model
LEFT JOIN table_keys tk2 ON t2.model2 = tk2.model