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