How to left join on 2 fields using the same table-CodePudding

I have the following fiddle :

https://www.db-fiddle.com/f/TSmNDk3rMUtYCi8NNVJND/0

My goal is to left join on two fields but using the same table.

Here is my two table structure.

-------------
|    Tab1   |
-------------
| id        |
| group1_id |
| group2_id |
-------------


--------------
| groupTable |
--------------
| id         |
| name       |
--------------

Here is some data for example :

Tab1 :

------------------------------
| id | group1_id | group2_id |
------------------------------
| 1  | 3         | 1         |
| 2  | 3         | 2         |
| 3  | 1         | 3         |
------------------------------

groupTable :

--------------
| id | name  |
--------------
| 1  | cars  |
| 2  | bikes |
| 3  | boats |
--------------

And the expected result :

------------------------
| id | group1 | group2 |
------------------------
| 1  | boats  | cars   |
| 2  | boats  | bikes  |
| 3  | cars   | boats  |
------------------------

My test requests are in the fiddle

CodePudding user response：

Well, the same table can be LEFT JOIN twice.

The query is:

SELECT Tab1.id, g1.name AS group1, g2.name AS group2
FROM Tab1
LEFT JOIN groupTable g1 ON Tab1.group1_id = g1.id
LEFT JOIN groupTable g2 ON Tab1.group2_id = g2.id

The DB Fiddle is updated: https://www.db-fiddle.com/f/TSmNDk3rMUtYCi8NNVJND/1