Is it possible to make the code below work. At the moment it only show employees from "New York" but not employees from "Chicago".?
SELECT employees.ename, zipcodes.city
FROM employees
LEFT JOIN zipcodes
ON employees.zip=zipcodes.zip
WHERE employees.zip =
(
SELECT zipcodes.zip
FROM zipcodes
WHERE zipcodes.city = 'New York' OR 'Chicago'
)
ORDER BY employees.eno;
Thanks in advance!
//René
Use the IN condition, like this:
SELECT employees.ename, zipcodes.city
FROM employees
LEFT JOIN zipcodes
ON employees.zip=zipcodes.zip
WHERE employees.zip IN
(
SELECT zipcodes.zip
FROM zipcodes
WHERE zipcodes.city IN ('New York', 'Chicago')
)
ORDER BY employees.eno
Another approach (possibly better one) is to use the JOIN condition itself instead of a sub query, like this:
SELECT employees.ename, zipcodes.city
FROM employees
LEFT JOIN zipcodes
ON employees.zip=zipcodes.zip
AND zipcodes.city IN ('New York', 'Chicago')
ORDER BY employees.eno
OR should be like this WHERE zipcodes.city = 'New York' OR zipcodes.city = 'Chicago'. Alternatively use the IN condition in the WHERE clause as well. I have updated my answer above.