subquery or join?

Question

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'

`=` compares 1 value to one value. The result of your sub query could be many job ID's. Therefore you can't use `=` nor `!=` for a comparison. you could use `IN` however instead of `=` — xQbert, Nov 27 '17 at 18:18
@PatrickHonorez yes, it works now. Thank you, I didnt see it — ANNA, Nov 27 '17 at 19:25

score 1 · Answer 1 · answered Nov 27 '17 at 18:18

1

If you run the subquery alone it shold show you more than one row.

As you are doing an equal (job_id = something), that something, as it is a query, is has to deliver a single value as a result which implicitly can't come in more than one row).

answered Nov 27 '17 at 18:18

Juan

5,525
2
15
26

score 1 · Accepted Answer · answered Nov 27 '17 at 18:19

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'))

subquery or join?

2 Answers2