Search code examples
mysqlsqldbvisualizer

subquery or join?

I am using subquery and join to execute this statement: SHOW ALL THE JOB TITLES THAT EXIST IN FINANCE DEPARTMENT (DO NOT REPEAT ANY JOB TITLES)

my Join works and I get right output, but subquery doesn't and I can't see an error output gives me this :

error message 

select DISTINCT(job_title) 
from jobs 
where job_id = (select job_id from employees 
                where department_id = 
                (select department_id from departments
                where department_name like 'finance'))



select DISTINCT(job_title) from jobs j
inner join employees e 
on j.job_id = e.job_id
inner join departments d
on d.department_id = e.department_id
where department_name like 'finance'
Solution

  • Without testing, you probably want:

    select DISTINCT(job_title) 
from jobs 
where job_id IN (select job_id from employees 
                where department_id IN 
                (select department_id from departments
                where department_name like 'finance'))