Thread: sql query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    4
    Rep Power
    0

    sql query


    Hi,
    I have two tables like this:

    Table-1

    Route|srcid|destid
    A 1 2
    B 3 4

    Table-2

    id | Name
    1 ValX
    2 ValY
    3 ValM
    4 ValN

    Can any one help me write query which returns this

    Route|Srcid|destid|SrcName(Name)|DestName(Name)
    A 1 2 ValX ValY
    B 3 4 ValM ValN

    Thanks in advance

    bskr
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select route,scrid,
      (select name from t2 
         where id = t1.scrid)  as srcName,
     destid,
     (select name from t2
        where id = t1.destid)  as DestName
      from t1
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    4
    Rep Power
    0
    Thank you. I'll try that.

    bskr
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    If you want to do it in one query (or you are forced to, like in MySQL < 4.0.9), try:

    SELECT t1.Route, t1.srcid, t1.destid, alias1.Name AS Src, alias2.Name AS Dest FROM t1, t2 AS alias1, t2 AS alias2 WHERE t1.srcid = alias1.id AND t1.destid = alias2.id

IMN logo majestic logo threadwatch logo seochat tools logo