I am trying to correct this error code in T-SQL that states that I have an invalid column

Question

This was my original question:

“What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your answers”

After using Mitch Wheat’s example (and taking his well deserved admondishment), I added a column to my DEPARTMENT table in my database and implemented this code:

ALTER TABLE DEPARTMENTS  
ADD Salary money
GO
select First_Name, Last_Name, department_Name  
from Employees e join  
   (select Department_Name,AVG(Salary) AS averageSalary  
     from DEPARTMENTS d  
     join Employees e ON e.Department_Id=d.Department_Id  
     group by  Department_Name) ds 
on ds.averageSalary=e.Employee_Id 
where e.salary>ds.AverageSalary 

However, I still got this error:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'salary'.

Why does SALARY continues to be an invalid column name?

Answer 1

You've perhaps got some case sensitive collation enabled, which would affect the case sensitivity of column names. Note your added column is 'Salary', but your query references 'salary'.

Correct the case and you should be fine.

See this related question for more on this.

(Note also LittleBobbyTables (great nick, btw!) comment about your query - there's an odd join clause there!)

Answer 2

Are you sure you have a salary column in Employee table? (e.salary) The Salary column you added is on Department table (d.Salary) to store the average salary of the department I suppose.

Also as Paul Dixon stated, case of column names can be important too.