Left Outer Join subqueries?

Question

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

----------
Project_Account
----------
actno
actname
projno
projname
ProjEmpID
ProjEmpMGRID

Where ProjEmpID, ProjEmpMGRID is user_id, and ProjEmpMGRID can be null. I need to find the user number and display the project_account table. I need to query with actNo that has duplicate values.

My request is as follows:

 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 returns a single-line subquery of more than one row

Since my subquery returns more than one email id, I get this error. As I said, the no action is not unique. I could understand the error, but I could not understand the solution. I am doing a left outer join in a subquery because there can be zeros in the prog manager identifier.

Any help would be appreciated.

Answer 1

, , , ( project_manager program_manager) . , "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'

Answer 2

- :

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'

, .

Answer 3

Why don't you just 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