使用IN子句的子查询 [英] subquery using IN clause

问题描述

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

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

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

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

我需要显示用户电子邮件ID.表之间的关系如下所示: 在Employer(1& 2)表中，EmpID包含User表中的UserID值.

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.

雇主编号"与项目"表中的值相关. EmpNo包含ProjEmpID，ProjEmpMGRID中的值.

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)

但是我在子查询中收到错误，指出IN子句中的参数太多.还有其他方法可以重写查询以获取结果.任何帮助，将不胜感激.

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.

推荐答案

您只能从子查询中返回一列.如果您只想同时使用员工ID和经理ID，则可以将联合查询作为您的子查询:

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)

或重写以使用两个IN查询，每个查询具有单独的子查询.

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

这篇关于使用IN子句的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！