mySQL select from different rows same table

Question

Suppose I have the following table:

|   ID   |   col1_fix   |   col2   |  col3   |   ref_ID  
    1        val1       val12     val13  
    2        val2       val22     val23        1
    3        val3       val32     val33      

What statement should I use to output to: (row id=2 has ref_id = 1 so instead of getting its value, it will get value from row id=1 instead, but I want to keep col1_fix value from row id=2, so this row will only end up getting col2 and col3 value from row id = 1)

|   ID   |   col1_fix   |   col2   |  col3   |     
    1        val1       val12     val13  
    2        val2       val12     val13        
    3        val3       val32     val33      

I'm thinking about creating a view so it will join its own table not not sure if its the right direction).

Answer 1

create table t1
(
id int not null auto_increment primary key,
col1 int not null,
col2 int not null,
col3 int not null,
ref_id int null
);

insert t1 (col1,col2,col3,ref_id) values (1,2,3,null);
insert t1 (col1,col2,col3,ref_id) values (222,223,224,null);
insert t1 (col1,col2,col3,ref_id) values (333,334,335,null);
insert t1 (col1,col2,col3,ref_id) values (444,445,446,null);
insert t1 (col1,col2,col3,ref_id) values (555,556,557,3);
insert t1 (col1,col2,col3,ref_id) values (666,667,668,2);

select one.id,
( case when one.ref_id is null then one.col1 else two.col1 end
) as col1,
( case when one.ref_id is null then one.col2 else two.col2 end
) as col2,
( case when one.ref_id is null then one.col3 else two.col3 end
) as col3
from t1 one
left join t1 two
on two.id=one.ref_id

Answer 2

select curr.id,
       curr.col1_fix,
       case when other.id is null then curr.col2 else other.col2 end as col2,
       case when other.id is null then curr.col3 else other.col3 end as col3
from the_table as curr
left join the_table as other
          on other.id = curr.ref_id;

However, I agree with Jim Garrison's comment that you didn't specify what to do if the ref_id value points to a row that itself has a ref_id value that points to another row, and so on, and so forth...

The above query doesn't attempt to deal with that situation. If you need to handle that kind of recursive requirement, then, my understanding is that you'll have a hard time making that work with MySql, because it lacks some basic recursion features. Though I'm sure that, if needed, someone more clever than me can show us how this is still doable.