CODEWITHSUNDEEP
mysqlsubquery
VanDerPrygel
VanDerPrygel

Reputation: 91

MySQL subqueries multiple result

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é

Upvotes: 0

Views: 45

Answers (1)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

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

Upvotes: 1

Related Questions