Quick query help

[pwoneill]

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?

[TimHall]

I would do it this way also!

[jmodic]

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):

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...

[sambavan]

Originally posted by TimHall
I would do it this way also!

Which way?

Sam