左外部加入子查询?
问题描述:
----------
User
----------
user_ID(pk)
UserEmail
----------
Project_Account
----------
actno
actname
projno
projname
ProjEmpID
ProjEmpMGRID
其中ProjEmpID,ProjEmpMGRID是user_id,ProjEmpMGRID可以为null。 我需要查找useremail并显示表project_account。我需要查询具有重复值的actNo。左外部加入子查询?
我的查询是这样的:
select projno,projname,actno,actname,
(select u.user_email as project_manager from project_account c left outer join users u
on u.user_id = c.ProjEmpID where actno='some no')as project_manager,
(select u.user_email as program_manager from project_account c left outer join users u
on u.user_id = c.ProjEmpMGRID where actno='someno') as program_manager
from project_account where actno='someno'
该错误消息我得到了甲骨文:
ORA-01427单行子查询返回 多行
由于我的子查询返回多个电子邮件ID,我得到这个错误。正如我所说,行为不是独一无二的。我可以理解错误,但我无法弄清楚解决方案。我在子查询中执行左外部连接,因为可能在prog manager id中有空值。
任何帮助,将不胜感激。
答
您遇到的错误是您的一个子查询(针对project_manager或program_manager)根据您的条件给出多个ID。这种情况是有道理的,因为多个项目帐户可能具有相同的“actno”,因为您没有将此指定为Primarky项(pk),而是使用子查询直接加入到用户表中找到标识
select projno,projname,actno,actname,
project_user.user_email as project_manager,
program_user.user_email as program_manager
from project_account
left join User as project_user
on project_account.ProjEmpID = project_user.user_id
left join User as program_user
on project_account.ProjEmpMGRID = program_user.user_id
where actno='someno'
答
什么是这样的:
select c.projno, c.projname, c.actno, c.actname, u.user_email as project_manager, us.user_email as program_manager
from project_account c
left outer join users u
on u.user_id = c.ProjEmpID
left outer join users us
on us.user_id = c.ProjEmpMGRID
WHERE actno = 'someno'
这样,你没有运行子查询和返回多个结果,并试图将它们存储为一个值。
答
你为什么不简单地使用它?
select projno, projname, actno, actname, (select user_email from users where user_id = pa.projempid), (select user_email from users where user_id = pa.projempmgrid) from project_account pa
+1:打我给它 – 2010-08-30 19:43:14
thanks.it工作。 – jero 2010-08-30 20:27:55