INNER JOIN problem (simple) - SQL Server Forums

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

Transact-SQL (2000)

INNER JOIN problem (simple)

Author

Topic

Gideon
Starting Member

15 Posts

Posted - 2002-07-21 : 23:19:32

Hi,

I need to join 2 tables using 2 attributes. I try this in the FROM clause......

FocisUSR.FOCIS.PITM PIT
INNER JOIN FocisUSR.FOCIS.stem ON (pit.acct_base_n = st.acct_base_n)
INNER JOIN FocisUSR.FOCIS.refr ON (REFER_BRCH_N = pitm.REFER_BRCH_N),
INNER JOIN FocisUSR.FOCIS.refr ON (ref.REFER_SPLIT_C = pitm.REFER_SPLIT_C)

and get this error...........

Tables 'FocisUSR.FOCIS.refr' and 'FocisUSR.FOCIS.refr' have the same exposed names. Use correlation names to distinguish them.

What am I doing wrong here? And what are correlation names?

Thanks in advance

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-22 : 00:34:21

Hi

Looks to me like there is a bit of info you are not giving here.

Firstly, post the full query not just the from clause.
Secondly, are you getting this error in Query Analyzer ? Or when you try to access a column it via ADO ?

My hunch is you are trying via ADO or ASP.....

Damian

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-07-22 : 00:43:25

I think you just need to give each table an alias (as you have already done with PIT):

FocisUSR.FOCIS.PITM PIT
INNER JOIN FocisUSR.FOCIS.stem ST ON (pit.acct_base_n = st.acct_base_n)
INNER JOIN FocisUSR.FOCIS.refr REF1 ON (ref1.REFER_BRCH_N = pit.REFER_BRCH_N),
INNER JOIN FocisUSR.FOCIS.refr REF2 ON (ref2.REFER_SPLIT_C = pit.REFER_SPLIT_C)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-22 : 00:53:43

OK I'm slapping my forehead now... DUH

Thanks Tigger

Damian

Gideon
Starting Member

15 Posts

Posted - 2002-07-22 : 00:57:44

DOH.....yes that works...told you it was simple

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-07-22 : 01:58:34

Why not AND the 2 conditions

ie:


FocisUSR.FOCIS.PITM PIT 
INNER JOIN FocisUSR.FOCIS.stem ON (pit.acct_base_n = st.acct_base_n) 
INNER JOIN FocisUSR.FOCIS.refr ON (REFER_BRCH_N = pitm.REFER_BRCH_N  and REFER_SPLIT_C = pitm.REFER_SPLIT_C)

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources