Quick query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Quick query help

  1. #1
    Join Date
    Jun 2000
    Posts
    417
    One of the users has tables set up as


    MoveWork
    --------------
    AcctName
    MoveFromID
    MoveToID

    MoveFromID and MoveToID are keys into this table

    Regions
    ----------
    RegID
    RegName

    He wants to create a query for all accounts showing region names for move from, and move to.

    Eg

    Acct1 RegionA RegionB
    Acct2 RegionQ RegionZ

    I figured with that layout his only option was to include the regions table twice in the query, something like

    select m.acctname, ra.regname, rb.regname
    from movework m, regions ra, regions rb
    where m.movefromid = ra.regid and m.movetoid = rb.regid

    But something is itching me and I think there's a better way to do it but I can't come up with it. If there's not a better way with that layout, is there a better way to organize it?

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    I would do it this way also!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As Tim said, I'd normaly do it the same way, too. However there is another alternative, although I can't say if it's any better (I'd say it's about equal if your original query is executed with nested loops):

    Code:
    select
       m.acctname,
       (select regname from regions where regid = m.movefromid) region_from,
       (select regname from regions where regid = m.movetoid) region_to
    from movework m;
    Offcourse, the output of this query will be different if there is any moveID in the MOVEWORK table that has no corresponding entry in the REGIONS table - this query behaves the same as your's would if you had used outer joins...

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Originally posted by TimHall
    I would do it this way also!
    Which way?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

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


Click Here to Expand Forum to Full Width