Subqueries in MySQL

user_17177093 0
Viewed 38

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.

mysql sql
1 Answers

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