0

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?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
user933791
  • 381
  • 1
  • 3
  • 12

3 Answers3

2

You can use self join in order to show the image name for link id

select t.*,t1.Image as link_image
from t 
left join t t1 on(t.link = t1.ID)

Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0
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
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks. I already tried this though. It will only select the rows that have the ID and link. I need all the rows, but I need to substitute the images with the ones in the link field – user933791 Sep 01 '14 at 07:57
  • I added a `UNION` that gets the other rows. – Barmar Sep 01 '14 at 08:00
-1

Something like this:

(SELECT ID, Image from mytable WHERE link = '' )
UNION ALL
(SELECT m1.ID as ID, m2.Image as Image 
   FROM mytable AS m1 INNER JOIN mytable AS m2 ON ( m1.link=m2.ID )
)
Michael Livach
  • 518
  • 3
  • 13
  • That actually looks like it'll do what I need it too. You're selecting all the rows without the link, then joining the rows so we get all rows, not just the ones with the relationship – user933791 Sep 01 '14 at 08:00
  • 1
    @user933791 use a left join.. union will be a lot slower. – John Ruddell Sep 01 '14 at 08:02
  • @JohnRuddell maybe the table is too small to think about the speed. "Union" solves that ussie and keeps format of rows. – Michael Livach Sep 01 '14 at 13:33
  • @MichaelLivach and a left join doesn't keep the format? did you even look at mkhalid's answer? the demo posted shows that it will pull everything from the table and then add the image name if it is there.. and the code is much more concise. – John Ruddell Sep 01 '14 at 14:51
  • @JohnRuddell yes, i looked. At mkhalid's solution, you got 2 columns ("Image" and "link_image") instead one – Michael Livach Sep 02 '14 at 07:24
  • @MichaelLivach wow... this is sad.. how much do you know about sql? anything? you do realize that the demo posted solves the issue and you are just wanting to show less columns right?? here is the exact same query except instead of t.* (which is selecting all columns in the table) i just changed it to t.id. http://sqlfiddle.com/#!2/3402de/8 – John Ruddell Sep 02 '14 at 13:27
  • @JohnRuddell, i know enough about sql (since you're interested). And what about you? About your sqlfiddle: you completely lost Image field for rows 2 and 3. – Michael Livach Sep 03 '14 at 08:52
  • @JohnRuddell look at my solution: http://sqlfiddle.com/#!2/3402de/13 . And here patched (by me) mkhalid's answer: http://sqlfiddle.com/#!2/3402de/16 - that solution i expected from you. – Michael Livach Sep 03 '14 at 09:04
  • @MichaelLivach i know a bit about sql... if you didn't notice I asked in the comments on your post to show your expected result.. no one knew what it was... I didn't know you wanted to see the image in one field.. with that said I would recommend you use COALESCE(img, img).. just because COALESCE has been around for a long time and is a part of the standard.. see here http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster – John Ruddell Sep 03 '14 at 13:54