Best approach for joining a table twice

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best approach for joining a table twice

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-10 : 18:45:13

For simplicity I have 2 tables :
create table formations
(top int,
base int)

values in this table will be
top,base
1,4
2,1
2,3
3,5
3,3

Both of these values can be translated using a reference table.
create table R_formation_type(
formation_type varchar(20),
legacy code int)

this table will look like
zone top , 1
zone base , 2
infill , 3
gas water contact, 4

and so on.

Instead of 1,3 I want to display 'zone top' , 'infill'

This table is huge (about 14 million records currently to expand to 100 million +) so I'm looking for something optimized.

select top.formation_type , base.formation_type
from formations f inner join R_formation_type top on top.legacy_code = f.top
inner join r_formation_type Base on base.legacy_code = f.base

Is that the best way of going about that? 2 seperate innerjoins? Looking for any input.

edit : fixing typos... man I tpyes adb

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - M.E. on 10/10/2002 18:46:17

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-10 : 18:52:08

Just for curiosity why would you need to process all the data at once and return strings?

Also how many different combinations are possible?

If small I'm thinking that having the table already created with all the possible combination of keywords and ids could reduce your problem to 1 join???

Edited by - ValterBorges on 10/10/2002 18:55:36

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-10 : 19:00:01

FOr reports, I've simplified this slightly. The table actually has
well_ID, top_Depth, Base_depth, Top_formation_type, Base formation_type

One well can have over 1000 of these. In top_formation_type and base_formation type I get a single digit to translate from this other table. I hold the data :
well,top_depth,_base depth,top_formation,base formation
140959 , 1.00 , 1.09 , 1,2
In print offs and applications they would prefer to see
140959 , 1.00, 1.09 , Top marker, zone top

I guess my question is: is there a better join syntax so I don't have to do 2 inner joins to the same table and only have to do one?

-----------------------
SQL isn't just a hobby, It's an addiction

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-10 : 19:35:28

I don't know the answer to your question but I'm trying to offer an alternate solution.

If you have a relatively small number of top_formation, base_formation then why not have these generated ahead of time in a separate table with the corresponding values and then when it's time to create the report you only have to do 1 join.

Edited by - ValterBorges on 10/10/2002 19:37:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources