CODEWITHSUNDEEP
sql
spacitron
spacitron

Reputation: 459

Selecting multiple rows with a sub-query

The question asks "Display the names of all employees whose job title is the same as anyone in the sales dept" but 

SELECT name, job 
FROM Employer WHERE job=(SELECT job FROM employer WHERE dept = sales);

does not work because the sub-query returns more than one value. How do I work around this?

Upvotes: 2

Views: 128

Answers (2)

vlad
vlad

Reputation: 4778

@Parado has the simplest solution. Keep in mind, however, that the in keyword is really just a join. For example, your query is the same as:

SELECT e1.name, e1.job
FROM Employer e1
    JOIN Employer e2 on e1.job = e2.job
WHERE e2.dept = 'sales'

Upvotes: 0

Robert
Robert

Reputation: 25763

Try to use in

SELECT name, job 
    FROM Employer 
    WHERE job in (SELECT job FROM employer WHERE dept = sales);

Upvotes: 4

Related Questions