CODEWITHSUNDEEP
sqlsubquerywhere-clause
Bale Rudal
Bale Rudal

Reputation: 69

Call variable of subquery that located on the where clause

I have 3 tables:

  1. employees : have emp_no
  2. current_dept_emp : have emp_no, dept_no
  3. departments : have dept_name

I want to call emp_no, along with dept_name which should be 'Marketing'. But, that variable (dept_name) is not called in any select. How can I do so? Here is my code

select 
    e.emp_no,
    xxx
from employees as e
    join current_dept_emp cde 
    on e.emp_no = cde.emp_no
    where cde.dept_no in 
            (select cde2.dept_no
            from current_dept_emp cde2 
            join departments d2
            on cde2.dept_no = d2.dept_no 
            where d2.dept_name = 'Marketing')

I want to write that code on the xxx line.

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

The in is not doing anything useful. Just use joins for all three tables:

select emp_no, xxx
from employees e join
     current_dept_emp cde
     on e.emp_no = cde.emp_no join
     departments d
     on d.dept_no = cde.dept_no
where d.dept_name = 'Marketing'

Upvotes: 1

Related Questions