My first post here and I am desperate. Need this to complete the last 2 questions of my assignment.

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);

This is the code I wrote and it is outputting almost what I need. The problem in the assignment wants to display Employees or Guests that are from Winnipeg or London. My code gives me all the cities for each employee and guest. I know I need to filter for Winnipeg and London with something like WHERE CityName IN ('London','Winnipeg') But I don't know where it needs to go.

I also need to add that I cannot use JOIN or AND operators.

I know it might all sound confusing that is because it is to me so explaining my problem is not that simple. Thank you for the help.

CodePudding user response：

there are some possibilies

Fro example

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

or

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

I also need to add that I cannot use JOIN or AND operators.

comma separated tables are another notation for CROSS JOIN, so that is a very stupid request

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