CODEWITHSUNDEEP
sqlsimplify
Rodrigo Cavalcante
Rodrigo Cavalcante

Reputation: 1597

How do I simplify this query?

so my main goal with this query is to select all departments and employees under a manager named Mario Souza.

SELECT d.deptname,e.name FROM employee e JOIN department d ON e.dept = d.deptnum WHERE idmanager IN(
SELECT id FROM employee WHERE name = 'Mario Souza' 
) AND manager IN(
SELECT id FROM employee WHERE name = 'Mario Souza' 
)

And it's working, but is there a way I could store the first IN result so I could use it later after the AND operator?

Upvotes: 0

Views: 55

Answers (2)

Anthony Faull
Anthony Faull

Reputation: 17957

You can use EXISTS to match on multiple columns.

WHERE EXISTS
(
    SELECT *
    FROM employee AS manager
    WHERE manager.name = 'Mario Souza'
    AND manager.id = e.idmanager
    AND manager.id = d.manager
)

Upvotes: 2

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

You could use a JOIN with employee table. Simply put both manager and idmanager in ON clause.

Upvotes: 1

Related Questions