SQLTeam.com | Weblogs | Forums

Correlation name issue


#1

I would consider myself a novice at writing SQL statements. I am trying to use a correlation name but having no luck. Below are parts of my select statement and from statement and the errors I get.
I understand I get the 4104 errors because of the 207 errors. What I do not understand is why I get the 207 errors. Any help would be appreciated. Thank you.

part of select statement
A.CODE_TABLE.TYPE_CD,
B.CODE_TABLE.TYPE_CD,

part of from statement
LEFT JOIN CODE_TABLE A with(NOLOCK) ON ITEM.HAZARD_CD = CODE_TABLE.TYPE_CD
LEFT JOIN CODE_TABLE B with(NOLOCK) ON ITEM.COMDTY_CD = CODE_TABLE.TYPE_CD

errors I get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CODE_TABLE.TYPE_CD" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CODE_TABLE.TYPE_CD" could not be bound.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'CODE_TABLE'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'CODE_TABLE'.


#2

You defined an alias for CODE_TABLE (which is what you are doing in the following with the last "A". The optional keyword AS can be omitted, which you did)
LEFT JOIN CODE_TABLE AS A

Once you define an alias, you MUST use that alias in all other places. So your JOIN should be like this:

LEFT JOIN CODE_TABLE A with(NOLOCK) ON ITEM.HAZARD_CD = A.TYPE_CD

If you had defined an alias for the ITEM table, replace ITEM with that alias as well.
Also do a similar thing for CODE_TABLE which is aliased as B.

I would VERY STRONGLY recommend that you not use the with (NOLOCK) query hint. It can lead to all kinds of problems without really giving you anything much useful in return.


#3

Thanks James. I tried this and still got errors.

A.CODE_TABLE.TYPE_CD,
B.CODE_TABLE.TYPE_CD,

LEFT JOIN CODE_TABLE A ON ITEM.HAZARD_CD = A.TYPE_CD
LEFT JOIN CODE_TABLE B ON ITEM.COMDTY_CD = B.TYPE_CD

Msg 207, Level 16, State 1, Line 45
Invalid column name 'CODE_TABLE'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'CODE_TABLE'.


#4

Once you define an alias, you MUST use that alias in all other places.

Change your select to

A.TYPE_CD, 
B.TYPE_CD,

#5

Thank you!!