Left Outer Join with subqueries?
----------
User
----------
user_ID(pk)
UserEmail
----------
Project_Account
----------
actno
actname
projno
projname
ProjEmpID
ProjEmpMGRID
Where ProjEmpID,ProjEmpMGRID is the user_id and ProjEmpMGRID can be null. I need to look up the useremail and display the table project_account. I need to query with actNo which has duplicate values.
My query goes like this:
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'
The error message I get in Oracle:
ora-01427 single row subquery returns more than one row
As my subquery returns more than one email id, I get this error. As I said, act no is not unique. I could understand the error, but I couldn't figure out the so开发者_运维百科lution. I am doing a left outer join in a subquery because there might be nulls in prog manager id.
Any help would be appreciated.
The error you are getting is that one of your subqueries (either for project_manager or program_manager) is giving you back more than one ID based on your conditions. This kind of makes sense, since multiple project accounts could have the same "actno" since you haven't specified that as a Primarky Key (pk)
furhter, rather than using subqueries, just join directly to the user tables to find the IDs
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'
What about something like:
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'
This way you aren't running subqueries and returning multiple results and trying to store them as one value.
Why don't you simply use this?
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
精彩评论