Query rename table for chaining back through history to get "a" from "d"

Question

I need to be able to link current name d to a in a query. Basically, someone looking up the object, which was originally named a, has to be able to reference it by a,b,,c, or d but still be able to "get" a file named using a's value.

Assume:

object_rename table
--------------------
 new | old
-----|-----
 b   | a
 c   | b
 d   | c

Is this possible in a query given this table structure?

(Not sure what title to use here.)

i'm guessing you are excluding the possibility of using a programming language w/ it... right? — hummingBird, Oct 13 '16 at 19:16
@AlexeySoshin - Preferably not. I can solve that if I can limit the steps. Suffice it to say, it wasn't spec'd having more than one generational rename to begin with. I have no idea how volatile the renames are in practice. — Jared Farrish, Oct 13 '16 at 19:17
@hummingBird - I have PHP (see my above comment). I can a definite number of steps, but the preference is to query it with `n` number of steps. — Jared Farrish, Oct 13 '16 at 19:18

score 3 · Accepted Answer · edited May 23 '17 at 12:06

Here's without limits:

SELECT  @id :=
    (
    SELECT  old
    FROM    names
    WHERE   new = @id
    ) AS variable
FROM    (
    SELECT  @id := 'd'
    ) vars
STRAIGHT_JOIN
    names
WHERE   @id IS NOT NULL

Here's a sql fiddle: http://sqlfiddle.com/#!9/2531b/3

This has been taken from this question: Hierarchical queries in MySQL

You can probably modify it to give only one answer, but considering the fact you have PHP to work with it, I'll rest my case. I guess you could do it in MySQL by adding an iterator and then selecting from results with maximum iterator value or something alike.

score 1 · Answer 2 · edited Oct 13 '16 at 19:49

If the number of revisions can be limited, you can use a self join.

Consider table:

create table hist_table (new_name varchar(20), old_name varchar(20));

Query:

select h0.new_name, h1.new_name, h2...
from hist_table h0
left join hist_table h1 on h1.old_name = h0.new_name
left join hist_table h2 on h2.old_name = h1.new_name
...

Will give you the basic history result.

Not very effective, but hierarchical queries rarely are.

What I would suggest instead is to reorganize your data:

create table hist_table (
   id int primary key
 , object_id int
 , name varchar(20)
 , created timestamp
);

I'm omitting not null constraints and indexes.
ID is just a basic sequence. Object Id points to your entity, which probably has more than just a name. Names are all the names object had.

Yes, with foreknowledge I would've include the unique identifier that links all of them together (in this case, a VIN number). However, it wasn't spec'd as being name-volatile.... — Jared Farrish, Oct 13 '16 at 19:50

Query rename table for chaining back through history to get "a" from "d"

2 Answers2