Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: a very confusing query

    Hello MySQL gurus,

    I have been working on this query for some time and couldn't come up with something adequate to accomplish what I need (maybe it's because I am quite new with SQL). Anyways on to the problem itself:

    I have this one table called 'connects':

    connectionId aEndEntityId zendEntityId

    | 36995 | 293737 | 294148 |
    | 36998 | 293748 | 294161 |
    | 36994 | 293753 | 294165 |
    | 37001 | 293757 | 294169 |
    | 37000 | 293761 | 294173 |
    | 36999 | 293767 | 294182 |
    | 37002 | 293792 | 294178 |


    Now, the aEndEntityId and zEndEntityId are foreign keys refering to another table called 'entity' which has the following structure:

    entityId entityName
    | 293975 | PE90ANK03[ Lo0 ] |
    | 293976 | PE90SAM01[ Gi3/4] |

    etc..

    I want to build such a query that the aEndEntityId and zEndEntityId in the connects table should translate to the entityName in the entity table.

    So I would have something like this

    connectionId aEndEntityId zendEntityId

    | 36995 |PE90ANK03[Lo0] |PE90ANK04[Lo0] |
    | 36998 |PE90SAM01[Gi3/4] |PE90SAM02[Gi3/5] |


    Is there a way to replace both foreign keys with the entityName in the entity table they are refering to?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use tow joins
    select my, comma, separated, columns, A.ThisColumn,B.THisColumn from mytable
    join myothertable as A on A.Thiscolumn = mytable.Acolumn
    join myothertable as B on B.Thiscolumn = mytable.Bcolumn

    mysql joins - Google Search

    or better yet, read da manual: MySQL :: MySQL 5.1 Reference Manual :: 12.2.8.1 JOIN Syntax
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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