Oracle Single-row subquery returns more than one row error

Question

I have a scenario. Can this be done using one query ?

Table Company : Single Company information with CPK as primary key and one Manager,Lead,HR attached to it
Table Employees : For every Company there are Employees (1 or more can be up to 500) with unique empID and Manager,Lead,HR attached to it
Table Employees Information (for using Web application) : he is assigned with 1 or more Manager,Lead,HR (or can be ALL,ALL,ALL which means he can see everything)

So:

Company
--------
CPK (PK)
Manager
Lead
HR

Employees 
--------
empID (PK)
CPK (FK)
Manager
Lead
HR

EmployeesInfo
-------------
USER_ID (FK)
Manager
Lead
HR

Web --> When a user login he should get all company information.If he has access to that company or any employee within that company then that row is enabled else its greyed out(disable) if its "All" then he can edit every record

For Example: User1 is assigned to Manager1, Lead1 and HR1.
Then he can edit all records from Company where Manager = Manager1, Lead = Lead1 and HR = HR1.
Also records in Company which has employees with Company.CPK = Employee.CPK and Employee.Manager = Manager1 and Employee.Lead = Lead1 and Employee.HR = HR1

My query till now But

select t2.MANAGER from Employees t2 where t2.CPK  = t1.CPK

returns Multiple record which is expected what should i do ???

SELECT  t1.*,
      --All condition
      CASE WHEN (SELECT MANAGER FROM EmployeesInfo WHERE USER_ID=44) = 'All' then 1
           ELSE(
                --Check for Company
                CASE 
                WHEN t1.MANAGER  in (SELECT MANAGER FROM EmployeesInfo WHERE USER_ID=44) then 1
                WHEN t1.LEAD in (SELECT LEAD FROM EmployeesInfo WHERE USER_ID=44) then 1
                WHEN t1.HR  in (SELECT HR FROM EmployeesInfo WHERE USER_ID=44) then 1
           ELSE(
                --Check Employee M,L,HR for that Company 
                CASE
                WHEN (SELECT t2.MANAGER FROM Employees t2 WHERE t2.CPK  = t1.CPK) in 
                       (SELECT MANAGER FROM EmployeesInfo WHERE USER_ID=44) then 1
                WHEN (SELECT t2.LEAD FROM Employees t2 WHERE t2.CPK = t1.CPK ) in 
                       (SELECT LEAD FROM EmployeesInfo WHERE USER_ID=44) then 1
                WHEN (SELECT t2.HR FROM Employees t2 WHERE t2.CPK  = t1.CPK  ) in 
                       (SELECT HR FROM EmployeesInfo WHERE USER_ID=44) then 1
                ELSE 0 END
                )
           END
           )
      END AS Grey_Out 
FROM Company t1 
WHERE  t1.CPK  ='1234'

Finally I should get all Company with grey_out field as (1 or 0) , then I will use the Grey_Out field for finding whether it should be made editable.

Cyril Gandon Cyril Gandon · Accepted Answer · 2011-09-30T13:40:31

I got a headache just to understand your table design.

Have you heard about joins?

SELECT DISTINCT c.*, 
       CASE WHEN e.empid IS NOT NULL OR ei.USER_ID IS NOT NULL
            THEN 1
            ELSE 0
       END AS Grey_Out 
FROM Company c
    LEFT OUTER JOIN EmployeesInfo ei
        ON c.MANAGER = ei.MANAGER 
        OR c.LEAD = ei.LEAD
        OR c.HR = ei.HR
    LEFT OUTER JOIN Employees e
        ON e.CPK = c.CPK
        AND (
               e.MANAGER = ei.MANAGER
            OR e.LEAD = ei.LEAD
            OR e.HR = ei.HR
        )
WHERE c.CPK = '1234'
AND ei.USER_ID = 44

Oracle Single-row subquery returns more than one row error

2 Answers