Vfrdtyky

I need to get the salary of an employee through two different tables: his gains and his discounts. The relation from the table employee to the two tables is a many to many relation. So I need to take the employee_id, get all the gain_id in the employee_gains table, add all of them and subtract with the analogue result in the discounts.

I tried this creating a view for the salary:

  CREATE VIEW salary as 

select ((select sum(value) from gains 

where gain_id in (select gain_id from gain_employee where employee_id=2)) 

- 

(select sum(value) from discount 

where discount_id in (select gain_id from discount_employee where employee_id=2)));

However, this only (and successfully) gives me the salary for the employee with ID 2. But how can I make this generic? I want a view salary for all the employees.

I would suggest you to use two CTEs to calculate the gains and discounts and then do a FULL OUTER JOIN on the two sets. This will ensure that you get proper values such as 0 for missing gains or discounts for an employee_id. If you want to ignore them such cases just change it to a plain INNER JOIN

CREATE OR REPLACE VIEW V_salary AS  --give proper name to indicate it's a view 

WITH ge 

     AS (SELECT e.employee_id, 

                SUM(g.value) AS gain_value 

         FROM   gain_employee e 

                JOIN gains g --use left join if some employees don't 

                             --have an entry in gains

                  ON e.gain_id = g.gain_id 

         GROUP  BY e.employee_id), 

     de 

     AS (SELECT e.employee_id, 

                SUM(d.value) AS dis_value

         FROM   discount_employee e  

                JOIN discounts d --use left join if some employees don't 

                                 --have an entry in discount

                 ON  e.discount_id = d.discount_id 

         GROUP  BY e.employee_id) 

SELECT COALESCE(ge.employee_id, gd.employee_id), --gets you atleast one of 

                                                 --them when one may be missing. 

       COALESCE(ge.gain_value, 0) - COALESCE(de.dis_value, 0) AS salary 

FROM   ge 

       FULL OUTER JOIN de    -- to consider case where one of them is absent

                    ON ge.employee_id = de.employee_id;

This should do it -

CREATE VIEW salary as

select S1.employee_id, (S1.gains - S2.discounts) as salary

    from (select ge.employee_id, sum(g.value) as gains 

              from gain_employee ge, gains g

              where ge.gain_id = g.gain_id

              group by ge.employee_id) S1,

         (select de.employee_id, sum(d.value) as discounts 

              from discount_employee de, discounts d

              where de.doscount_id = d.discount_id

              group by de.employee_id) S2

    where S1.employee_id = S2.employee_id;

Then you can query this view with employee_id as the condition.