SQL Server:无法从另一个表中过滤相同的数据库问题，从而选择一个表的列

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:

我使用SQL Server 2014，我得到这个错误:

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

Msg 207，第16级，状态1，第51行无效列名“CITY_NAME”。

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

实际上我需要从EMP中获取EMPNO通过过滤所有来自洛杉矶的人

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.

谢谢你！

3 个解决方案

#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';

#2

If you have 3 tables:

如果你有三个表格:

EMP
电磁脉冲
LOCATION
位置
DEPT
部门

Query will be

查询将会

SELECT 
    EMP.* 
FROM 
    EMP 
JOIN 
    DEPT ON EMO.DEPTNO = DEPT.DEPTNO
JOIN
    LOCATION ON LOCATION.CITY_ID = DEPT.CITY_ID
WHERE 
    LOCATION.CITY_NAME = 'Los Angeles'

This can be achiveved by join.

这可以通过join来实现。

#3

you do not habe a column named city_name in emp table but city_id so you have a table named location with city_id and city_name as column

您在emp表中没有名为city_name的列，而是city_id，因此您有一个名为location的表，其中city_id和city_name作为列

you only habe to join the both table using city_id and you will be able to filter by the column city_name.

您只需使用city_id加入这两个表，就可以通过列city_name进行筛选。

SELECT EMPNO FROM EMP e, DEPT d, LOCATION l WHERE e.CITY_ID = l.CITY_ID AND e.DEPTNO = d.DEPTNO AND l.CITY_NAME = 'Los Angeles';

从EMP e中选择EMPNO, DEPT d, LOCATION l WHERE e。CITY_ID = l。CITY_ID和e。DEPTNO = d。DEPTNO和l。CITY_NAME =“洛杉矶”;

#1