I'm dealing with old database that contain a table with this kind of schema.
+----+------+--------------+ | id | name | updated_from | +----+------+--------------+ | 1 | A | NULL | | 2 | B | NULL | | 3 | AA | 1 | | 4 | BB | 2 | | 5 | AAA | 3 | +----+------+--------------+
What I try to do is to get the latest update with specific id, for example, if I want to know latest update from A with id=1 or AA which of course is AAA. Sadly, the schema doesn't have any timestamp column.
To make it easier, this is my expected result when I search for id=3.
+----+------+--------------+ | id | name | updated_from | +----+------+--------------+ | 1 | A | NULL | | 3 | AA | 1 | | 5 | AAA | 3 | +----+------+--------------+
I apologize for any unclear explanation and thanks in advance.
EDIT : To make it clearer, I hope this can help :
1:A:NULL -> 3:AA:1 -> 5:AAA:3
Which every update any row, the previous row copy the id and put it to updated_from column into the new row, this make them like chained.
Given that example, if I search for any arbitrary id, I will get the previous also the next chain id.
After reading @hofan41 comments, I've to come to this answer Generating Depth based tree from Hierarchical Data in MySQL (no CTEs).
which lead to my modified version of @f00 answer. Schema and example data exactly same with first table I presented.
drop procedure if exists `table1_hier`;
delimiter #
create procedure table1_hier (
in updated_from_id smallint unsigned
)
begin
declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;
drop temporary table if exists hier;
create temporary table hier (
updated_from smallint unsigned,
name_id smallint unsigned,
depth smallint unsigned default 0
) engine = memory;
insert into hier
select updated_from, id, v_depth
from table1
where id = updated_from_id;
create temporary table tmp engine = memory select * from hier;
while not v_done do
if exists(select 1
from table1 p inner join hier on p.updated_from = hier.name_id
and hier.depth = v_depth) then
insert into hier
select p.updated_from, p.id, v_depth + 1 from table1 p
inner join tmp on p.updated_from = tmp.name_id and tmp.depth = v_depth;
set v_depth = v_depth + 1;
truncate table tmp;
insert into tmp select * from hier where depth = v_depth;
else
set v_done = 1;
end if;
end while;
select
p.id,
p.name,
b.id as updated_from,
b.name as name_from,
hier.depth
from
hier
inner join table1 p on hier.name_id = p.id
left outer join table1 b on hier.updated_from = b.id
order by
hier.depth, hier.name_id;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
But if I searching with id=3 I only got the next chain id not the previous.
+----+------+--------------+-----------+-------+ | id | name | updated_from | name_from | depth | +----+------+--------------+-----------+-------+ | 3 | AA | 1 | A | 0 | | 5 | AAA | 3 | AA | 1 | +----+------+--------------+-----------+-------+
