Subtract two values from different tables [closed]

Question

Subtract two values from different tables [closed]

Navigation

#1 by (3 votes)

2

How could I subtract two values from different tables in SQL? I want a column to subtract the user's vacant days off. These days must be the difference between the days you have requested in the applications and the days you have vacations per year.

I have two tables:

solicitud : which is a request table that has as fields pk_solicitud , fk_empleado , n_diassolicitados .
sec_users : which is a user table with pk_user , name , dias_disponibles .

The fk_empleado of the request table is the foreign key for pk_user of the user table.

And I tried this query:

select u.dias_disponibles-s.N_DIASSOLICITADOS as dias_libres 
from solicitud s, sec_users u

Once you have already shown me the free days of each user, for example:

Usuario               Días libres
==============        ==========
Usuario 1                    10
Usuario 2                    12
Usuario 3                     8
Usuario 4                    11

I am trying to get him to leave the days he has requested. In the previous function I marked the ones that you have not ordered but now I want the ones you have requested. It would look like this:

Usuario             Vacaciones  Baja
==============    ============  ==== 
Usuario 1                10        1        
Usuario 2                12        0        
Usuario 3                 8        3        
Usuario 4                11        1

sql

asked by Alvaro Roman 26.02.2018 в 13:15

source

1 answer

Inquiry about Laravel and Eloquent ImageView with Android Java Notification Counter

score 3 · Answer 1

The problem is that since you are not doing any type of user id checking, what you will get as a result is a combination of all the users with all the requests. When you want to return the corresponding requests to each user.

For this you could do a JOIN or add the check in WHERE (in addition, since there can be several rows in request, you must group by the user id to do the sum):

SELECT   u.pk_user, u.name, 
         u.dias_disponibles - SUM(s.n_diassolicitados) AS dias_libres 
FROM     solicitud s, sec_users u
WHERE    s.fk_empleado = u.pk_user
GROUP BY u.pk_user;

That way you will return the remaining days for all users (with your id). If you want to be a particular user, add your id to WHERE :

SELECT u.dias_disponibles - SUM(s.n_diassolicitados) AS dias_libres 
FROM   solicitud s, sec_users u
WHERE  s.fk_empleado = u.pk_user 
  AND  u.pk_user = ID_DE_USUARIO;

Where USER_ID is the ID ( pk_user ) of the user from whom you want to get the remaining days off.

You can see both queries working in this SQL Fiddle .

For the last part of the question (added later), you want it to show the requested days. That would be as simple as adding the value of the sum as a column. So, you could have something like this:

-- mostrar días disponibles, solicitados y resta para todos los trabajadores
SELECT   u.pk_user, u.name, 
         u.dias_disponibles,
         SUM(s.n_diassolicitados) AS dias_solicitados,
         u.dias_disponibles - SUM(s.n_diassolicitados) AS dias_libres 
FROM     solicitud s, sec_users u
WHERE    s.fk_empleado = u.pk_user
GROUP BY u.pk_user;

-- mostrar días disponibles, solicitados y resta para un trabajador en concreto
SELECT u.dias_disponibles,
       SUM(s.n_diassolicitados) AS dias_solicitados,
       u.dias_disponibles - SUM(s.n_diassolicitados) AS dias_libres 
FROM   solicitud s, sec_users u
WHERE  s.fk_empleado = u.pk_user 
  AND  u.pk_user = 1; -- días disponibles para Alvaro

that you can see running in an update to the SQL Fiddle .