subquery using IN clause

Question

----------
User
----------
user_ID(pk)
UserEmail

----------
Employer1
----------
Emp1ID(pk)
Emp1NO

----------
Employer2
----------
Emp2ID(pk)
Emp2NO

----------
Project
----------
ProjEmpID
ProjEmpMGRID

I need to display User Email ID. The relation between the table goes like this: In the Employer(1&2) table EmpID contains the value of UserID in the User table.

the Employer No relates to the values from the Project table. EmpNo contains values from ProjEmpID, ProjEmpMGRID.

 select u.user_email from users u, Employer emp 
    where emp.Emp1ID =  u.user_id and 
    emp.Emp1NO IN
    (select ProjEmpID,ProjEmpMGRID from project)
union 
  select u.user_email from users u, Employer emp 
    where emp.Emp2ID =  u.user_id and 
    emp.Emp2NO IN
    (select ProjEmpID,ProjEmpMGRID from project)

but i get error in the subquery stating too many parameters on the IN clause.Is there any other way i could rewrite the query to get result. any help would be appreciated.

Answer 1

You can only return one column from your subquery. If you just want both the employee ID and manager ID consider a union query as your subquery:

    emp.Emp2NO IN
(select ProjEmpID from project
 union
 select ProjEmpMGRID from project)

or rewriting to use two IN queries with separate subqueries for each.

Answer 2

Eric Hauser is correct - you can't specify two columns in SELECT contained in an IN clause to one column outside.

That said, I re-wrote your query so you don't need to use UNIONs at all:

SELECT DISTINCT u.user_email
  FROM USERS u
  JOIN EMPLOYER e ON u.user_id IN (e.emp1id, e.emp2id) 
  JOIN PROJECT p ON e.emp1no IN (p.projempid, p.projempmgrid)
                 OR e.emp2no IN (p.projempid, p.projempmgrid)

I also changed the query to use ANSI-92 JOIN syntax - your original uses ANSI-89.

Answer 3

I would recommend not using the IN statement. Use EXISTS instead.

http://www.techonthenet.com/sql/exists.php

Example #1