Reference one record from another in the same table

I have a table containing data. When one record is marked as moved I set "moved_id" to the id of the new record. How can I read from the new record as if I were reading from the original with a single SELECT statement?

For example, when I get to record23, data is returned from record99 without having to do another SELECT statement.

Cheers

Stu

Look into UNION (http://dev.mysql.com/doc/refman/4.1/en/union.html).

I had a look at that but I can't see a way how to do in effect

if record25.moved then return record99 instead

Stu

SELECT
A.field1
FROM table as A
WHERE A.moved_id = ''
UNION
SELECT
B.field1
FROM table as B
WHERE B.id = A.moved_id
ORDER BY 1


Maybe...

Or using an "if" statement may be better...

SELECT
if (A.moved_id = '', A.field1, B.field1)
FROM table as A, table as B
WHERE A.moved_id = B.id


But, uh, this whole idea seems to be kind of weak imo.... there is probably a better way to do what you are trying to do.