MySQL，根据列值从不同的行中选择

Question

I have the following table

---------------------------
|ID  |Image      | link    |
|01  |           | 02      |  
|02  |steve.jpg  |         |         
|03  |eric.jpg   |         |         
----------------------------

The link field stores IDs, so I can set a relationship between two rows.

The ID 01 has its link field set to 02. So I want ID 01 to use the image whose ID is set in that field, so it'll be steve.jpg.

How can I get all rows, so that the ones with the link field set end up with that Image?

Answer 1

SELECT a.id, b.image, a.link
FROM YourTable AS a
JOIN YourTable AS b ON a.link = b.id
UNION
SELECT id, image, link
FROM YourTable
WHERE link IS NULL