MySQL中的子查询

Question

我在这里的第一个帖子，我很绝望。我需要这个来完成作业的最后两个问题。

SELECT "Employee"        AS PersonType,
EmployeeFirstName AS FirstName,
EmployeeLastName  AS LastName,
CityName
FROM employee, cities
WHERE CityName IN (SELECT CityName WHERE employee.CityID = cities.CityID)
UNION
SELECT "Guest"        AS PersonType,
GuestFirstName AS FirstName,
GuestLastName  AS LastName,
CityName
FROM guest, cities
WHERE CityName IN (SELECT CityName WHERE guest.CityID = cities.CityID);

这是我写的代码，它几乎输出了我需要的东西。任务中的问题希望显示来自温尼伯或伦敦的Employees或Guests。我的代码为我提供了每个员工和客人的所有城市。我知道我需要用WHERE CityName IN ('London','Winnipeg')之类的东西来过滤温尼伯和伦敦，但我不知道它需要去哪里。

我还需要补充一点，我不能使用JOIN或AND运算符。

我知道这可能听起来令人困惑，因为对我来说，解释我的问题并不那么简单。谢谢你的帮助。

Answer 1

有一些可能性

来回例子

SELECT 
"Employee" AS PersonType
, e.EmployeeFirstName AS FirstName 
, e.EmployeeLastName AS LastName
, c.CityName 
FROM employee e INNER JOIN  cities c ON  e.CityID = c.CityID
WHERE c.CityName IN ('London','Winnipeg') 
UNION 
SELECT 
"Guest" AS PersonType
, g.GuestFirstName AS FirstName 
, g.GuestLastName AS LastName
, c.CityName 
FROM guest g INNER JOIN  cities c ON g.CityID = c.CityID
WHERE g.CityName IN ('London','Winnipeg');

或

SELECT * FROM    
(SELECT 
"Employee" AS PersonType
, e.EmployeeFirstName AS FirstName 
, e.EmployeeLastName AS LastName
, c.CityName 
FROM employee e INNER JOIN  cities c ON  e.CityID = c.CityID
UNION 
SELECT 
"Guest" AS PersonType
, g.GuestFirstName AS FirstName 
, g.GuestLastName AS LastName
, c.CityName 
FROM guest g INNER JOIN  cities c ON g.CityID = c.CityID) t1
WHERE CityName IN ('London','Winnipe
g');

我还需要补充，我不能使用JOIN或AND操作符。

逗号分隔的表是CROSS JOIN的另一种符号，所以这是一个非常愚蠢的请求

SELECT * FROM    
(SELECT 
"Employee" AS PersonType
, e.EmployeeFirstName AS FirstName 
, e.EmployeeLastName AS LastName
, c.CityName 
FROM employee e,  cities c 
where  e.CityID = c.CityID
UNION 
SELECT 
"Guest" AS PersonType
, g.GuestFirstName AS FirstName 
, g.GuestLastName AS LastName
, c.CityName 
FROM guest g ,cities c WHERE g.CityID = c.CityID) t1
WHERE CityName IN ('London','Winnipeg');