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).

So if it contains 2 or more rows that is what u want? 1 million rows u want first 2 double up'd sorta, then the 999998 to follow? — AsConfused, Jun 14 '15 at 04:03
I want to get the same number of rows. But instead of selecting value from a row, if that row has a ref_id, then it will select (part of) other row with id = ref_id instead. So if there is 1 million rows, output will be 1 million rows — blues, Jun 14 '15 at 04:06
What happens in your example if row 1 has a `ref_ID` of 3? Do you need to follow an arbitrarily long chain of references? Your question is under-specified, you need to consider all possibilities and decide what you want for each case. — Jim Garrison, Jun 14 '15 at 04:33
Actually they are part of a "group" (there are other ID used to track this but I didn't list) and can only have one unique ref_ID, so there will be no chain of references. Sorry for confusion. — blues, Jun 15 '15 at 03:31

score 0 · Answer 1 · answered Jun 14 '15 at 04:43

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

score 0 · Answer 2 · answered Jun 14 '15 at 05:46

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.

mySQL select from different rows same table

2 Answers2