SQL Server : can't select columns of one table by filtering from another table on same database issue

Question

I am using this statement to fetch the rows from one table by filtering the data from another table.

 SELECT EMPNO 
 FROM EMP 
 WHERE CITY_NAME = 'Los Angeles';

I am using SQL Server 2014 and I get this error:

Msg 207, Level 16, State 1, Line 51
Invalid column name 'CITY_NAME'.

Actually I need to fetch EMPNO FROM EMP by filtering all people from 'Los Angeles'

I also try this statement but it doesn't work either:

SELECT EMPNO 
FROM EMP 
WHERE dbo.LOCATION.CITY_NAME = 'Los Angeles';

NOTE: these are all on the same server and same database

Thank you.

Answer 1

You need JOIN between the tables.

 SELECT EMPNO FROM EMP 
 JOIN DEPT ON EMP.DeptNo = DEPT.DeptNo
 JOIN LOCATION ON LOCATION.City_ID = DEPT.City_ID 
 WHERE LOCATION.CITY_NAME='Los Angeles';