CODEWITHSUNDEEP
mysqlsql
Christian Plan
Christian Plan

Reputation: 11

Why is "EXISTS" subquery not limiting to both WHERE conditions?

New to programming here. I want to pull all the information in the 'employees' table along with their job title from another table but limited to 'Assistant Engineer' by using the 'EXISTS' subquery.

I tried this approach:

select e.*, t.title
from employees e
join titles t on t.emp_no = e.emp_no
where exists (
  select * from titles t
  where t.emp_no = e.emp_no
  and t.title = 'Assistant Manager'
)

When you remove the 'Assistant Engineer' condition and put it outside the subquery, it works but I want to check if it's possible to put it inside the subquery.

Thank you very much for your help

Upvotes: 0

Views: 68

Answers (1)

forpas
forpas

Reputation: 164089

It will work if you remove the join which is useless and wrong in your case:

select e.*, 'Assistant Manager' title
from employees e
where exists (
  select 1 from titles t
  where t.emp_no = e.emp_no
  and t.title = 'Assistant Manager'
)

If you want to use a join then there is no need for EXISTS:

select e.*, t.title
from employees e inner join titles t
on t.emp_no = e.emp_no and t.title = 'Assistant Manager'

Upvotes: 1

Related Questions