...

View Full Version : Reference one record from another in the same table



Afrow UK
03-06-2007, 06:14 PM
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

Fumigator
03-06-2007, 06:49 PM
Look into UNION (http://dev.mysql.com/doc/refman/4.1/en/union.html).

Afrow UK
03-07-2007, 12:51 AM
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

Fumigator
03-08-2007, 03:46 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum