Jared Farrish Jared Farrish - 1 month ago 8
SQL Question

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

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

Answer

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.