SQL Subquery with JOIN

database, oracle, sql

Solution

To simplify your query, you can use:

select emp1.ename, emp1.job 
from EMP emp1 
where emp1.deptno in (SELECT DEPT.DEPTNO from DEPT where DEPT.loc = 'CHICAGO');

To select name and job from emp1 where emp1's job is same as emp2's job with location Chicago:

SELECT emp1.ename, emp1.job
FROM emp emp1
WHERE emp1.job IN (SELECT emp2.job
                      FROM emp emp2
                      FULL JOIN dept ON (emp2.deptno = dept.deptno)
                      WHERE dept.loc = 'CHICAGO');

To select name and job from emp1 if there exists atleast one record with location in chicago.

  SELECT emp1.ename, emp1.job
    FROM emp emp1
    WHERE  EXISTS (SELECT emp2.job
                   FROM emp emp2
                   FULL JOIN dept ON (emp2.deptno = dept.deptno)
                   WHERE dept.loc = 'CHICAGO');

In your case, i assume that the first and second query would be more appropriate.

Problem

I'm trying to learn Subquerys. I have troubles with this: The two tables: ``` CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY, LOC varchar2(3)); CREATE TABLE EMP (ENAME varchar2(10), JOB varchar2(9), DEPTNO NUMBER(2) NOT NULL CONSTRAINT EMP_FOREIGN_KEY REFERENCES DEPT (DEPTNO)); ``` I want to get the name (emp.ename) and the job (emp.job) but only where the job also exists in 'CHICAGO'. This is what I have done: ``` SELECT emp1.ename, emp1.job FROM emp emp1 WHERE emp1.job EXISTS (SELECT emp2.job FROM emp emp2 FULL JOIN dept ON (emp2.deptno = dept.deptno) WHERE dept.loc = 'CHICAGO'); ``` I always get the "invalid relational operator" error in the line 3. Example of the outcome: ``` ENAME | JOB | LOC JONES | SALE | CHICAGO FORD | SALE | NEW YORK //He doesn't sit in CHICAGO but the job also exists in Chicago ```

Original source