-
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?
-
I would do it this way also!
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|