Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Dec 2002
    Location
    UK
    Posts
    177
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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
    if ($ENV{'QUERY_STRING'} eq "Afrow UK") {
    print "$ENV{'QUERY_STRING'} rocks!";
    } else {
    print qq~$ENV{'QUERY_STRING'} sucks :)~;
    }

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Look into UNION.

  • #3
    Regular Coder
    Join Date
    Dec 2002
    Location
    UK
    Posts
    177
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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
    if ($ENV{'QUERY_STRING'} eq "Afrow UK") {
    print "$ENV{'QUERY_STRING'} rocks!";
    } else {
    print qq~$ENV{'QUERY_STRING'} sucks :)~;
    }

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Code:
    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...
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •