title: ORACLE: multiple column subqueries
body: <p>This is from Oracle Database 10g: SQL Fundamentals.
  Original Oracle textbook.
  Francly speaking, this all is actual so far.</p>
  
  <p>There is a task which troubles me:</p>
  
  <p><strong>Display the last name, department name, and salary of any employee whose salary and commission match the salary and commission of many employee located in location ID 1700.</strong></p>
  
  <p>The topic to be learned is multiple column subqueries. This mean that we can't deviate from the offered model which is: </p>
  
  <pre><code>SELECT column, column, ...
  FROM table
  WHERE (column, column, ...) IN
  (SELECT column, column, ...
  FROM table
  WHERE condition);
  </code></pre>
  
  <p>Well, my database:</p>
  
  <pre><code>describe employees
  Name           Null     Type         
  -------------- -------- ------------ 
  EMPLOYEE_ID    NOT NULL NUMBER(6)    
  FIRST_NAME              VARCHAR2(20) 
  LAST_NAME      NOT NULL VARCHAR2(25) 
  EMAIL          NOT NULL VARCHAR2(25) 
  PHONE_NUMBER            VARCHAR2(20) 
  HIRE_DATE      NOT NULL DATE         
  JOB_ID         NOT NULL VARCHAR2(10) 
  SALARY                  NUMBER(8,2)  
  COMMISSION_PCT          NUMBER(2,2)  
  MANAGER_ID              NUMBER(6)    
  DEPARTMENT_ID           NUMBER(4)    
  
  describe departments
  Name            Null     Type         
  --------------- -------- ------------ 
  DEPARTMENT_ID   NOT NULL NUMBER(4)    
  DEPARTMENT_NAME NOT NULL VARCHAR2(30) 
  MANAGER_ID               NUMBER(6)    
  LOCATION_ID              NUMBER(4)  
  </code></pre>
  
  <p>There is a solution:</p>
  
  <pre><code>select
    e.last_name,
    d.department_name, 
    e.salary
  from employees e, departments d
  where e.department_id = d.department_id
  and (salary, nvl(commission_pct, 0)) in
    (select salary, nvl(commission_pct, 0)
     from employees e, departments d
     where e.department_id = d.department_id
     and d.location_id = 1700);
  </code></pre>
  
  <p>SELECTED 36 ROWS.</p>
  
  <p>Well, I started checking. It seems that employees from location 1700 duplicated against themselves.
  Look: we take the whole list of employees and then juxtapose it with employees from location 1700. Of course, there will be duplicates. </p>
  
  <p>I have prepared an excel file with some data (all employees, employees from location 1700, the result of my own desired result etc.). In the file it is more picturesque, with examples and so on. Please, have a look. </p>
  
  <p>This is the file: <a href="https://skydrive.live.com/redir?resid=8CDFFBA921B002FE!150&amp;authkey=!ADMRAE466BIunQM" rel="nofollow">https://skydrive.live.com/redir?resid=8CDFFBA921B002FE!150&amp;authkey=!ADMRAE466BIunQM</a></p>
  
  <p>Well, what I would like to do is to control that no employee is compared with themselves.
  This was my variant bevore I checked the solution.</p>
  
  <pre><code>select lnme, dpt, slry 
  from
  (
    select       
        employee_id eid,
        last_name lnme, 
        salary slry, 
        nvl(commission_pct,0) cpct,
        d.department_name dpt,
        location_id
    from employees e
    left join departments d
    on e.department_id = d.department_id
  )
  where (slry, cpct) in
  (select 
      employee_id ide,
      salary slry, 
      nvl(commission_pct,0) cpct    
  from employees e
  join departments d
  on e.department_id = d.department_id and location_id = 1700)
  and ide &lt;&gt; eid
  </code></pre>
  
  <p>I wanted to make sure that no employee is compared with the same employee. But I failed.</p>
  
  <p>Could you comment on all this situation:
  1. Whether I'm completely wrong and the solution of Oracle is perfect.
  2. If Oracle is wrong, could you help me with this code?</p>
  
  <p>Thank you in advance.</p>