CODEWITHSUNDEEP
sqlsql-servert-sqlalias
Sean Smyth
Sean Smyth

Reputation: 1581

Formatting sql when using aliases

I have this block of sql in a script:

update #Result
Set FacilityTotals = 'Facility Total: IS Needed Yes: ' + 
                     (select count(ISNeeded_YN) 
                      from #ISReview C, #Result R 
                      where CustomerID = C.CustomerID and ISNeeded_YN = 'Y') + 
                     ' No: ' + 
                     (select count(ISNeeded_YN) 
                      from #ISReview C 
                      where CustomerID = C.CustomerID and ISNeeded_YN = 'N') + 
                      ' IS Yes: ' + 
                     (select count(IS_YN) 
                      from #Result C 
                      where CustomerID = C.CustomerID and IS_YN = 'Y') + 
                     ' No: ' + 
                     (select count(IS_YN) 
                      from #Result C 
                      where CustomerID = C.CustomerID and IS_YN = 'N')

When I try to run it, I get the following errors:

Msg 209, Level 16, State 1, Line 500
Ambiguous column name 'CustomerID'.
Msg 209, Level 16, State 1, Line 500
Ambiguous column name 'ISNeeded_YN'.
Msg 209, Level 16, State 1, Line 500
Ambiguous column name 'ISNeeded_YN'.

ISNeeded_YN is in the #isReviewTable, while CustomerID is in both tables. I'm pretty new to this type of sql, how do I write this correctly?

Upvotes: 0

Views: 682

Answers (1)

Michael
Michael

Reputation: 599

You need to prefix your columns with the table name.

For example,

This piece.

CustomerID = C.CustomerID

It knows that the right side is from #IsReview, but it has no idea which table to use for left side. It might be obvious to you but sql only knows that you have that column on multiple tables and it doesn't know what to do.

R.CustomerID = C.CustomerID would resolve the issue for you in that particular location.

You just need to do that where ever you are referencing your columns that are used in more than one table.

Upvotes: 4

Related Questions