In SQL, how can you select and match more than one column in a sub-select?

smallwei 10:54 pm on October 28, 2009 | Answers: 2    
Tags: column (9), emp, from (4), oracle (136), promotion list, select (12), sql (43), sql statement (2)

Rate Knowledge:

 Loading ...

SELECT * FROM emp
WHERE id IN (SELECT emp_id FROM promotion_list WHERE year = '2009');

• smallwei 10:54 pm on October 28, 2009
  

  You can use the following syntax to accomplish multiple columns sub-select:

  SELECT * FROM t1
  WHERE (a, b) IN
  (SELECT y, z FROM t2)

  For example:
  

  SELECT * FROM emp
  WHERE(dept, title) IN 
  (SELECT emp_dept, emp_title FROM promotion_list WHERE year = '2009');
  

• smallwei 10:44 pm on November 3, 2009
  

  Another way is to use the following statement: (thanks to budmanz)

  SELECT *
  FROM emp
  WHERE EXISTS (SELECT 'X'
  FROM promotion_list
  WHERE year = '2009' AND dept = emp_dept AND title = emp_title);