Join bringing wrong value

Navigation
2

I have two tables

Company: 

id_empresa   id_usuario   vl_honorario   dt_honorario   id_honorario
   86             1            200       2017-01-04           7
   86             1            600       2016-12-01           6
   86             1            500       2016-11-01           5
   86            212           300       2016-12-01           5

Fee: 

id_honorario   tp_honorario  
   5             Mensal                 
   6             Anual
   7             Trimestral

I am attempting to group the amount of the vl_honorario Fee if the date of the fee is equal to month 12 or if the Type of Fee tp_honorario = 'Monthly'. Sql looks like this: 

SELECT SUM(h.vl_honorario) as vl_honorario, h.id_usuario 
FROM empresa as h 
INNER JOIN honorario as p ON p.tp_honorario = 'Mensal' 
WHERE h.id_empresa = 86 
AND MONTH(h.dt_honorario) = 12 
GROUP by h.id_usuario

And it returns me: 

vl_honorario   id_usuario  
   600             1                 
   300            212

But I should return the result below, because in 2016-11-01 I have the value of 500, which should be being considered since the type of the fee is Monthly 

vl_honorario  id_usuario  
    1100          1                 
    300          212
    
asked by anonymous 02.12.2016 / 12:12

2 answers

1

You are misusing INNER JOIN , linking to what should be your OR condition: 

SELECT SUM(h.vl_honorario) as vl_honorario,
       h.id_usuario
  FROM empresa as h
       INNER JOIN honorario as p ON p.id_honorario = h.id_honorario
 WHERE h.id_empresa = 86
   AND (MONTH(h.dt_honorario) = 12
    OR p.tp_honorario = 'Mensal')
 GROUP by h.id_usuario
    
02.12.2016 / 12:19
1

There are two points you need to take care of. Your relationship between the ON p.id_honorario = h.id_honorario tables, leave the filter to clausular and p.tp_honorario = 'Mensal' , something else when you do AND MONTH(h.dt_honorario) = 12 you return every month that are equal to 12, and in your sample there are only 2 months, so its sum will not have more than the first result you showed. 

SELECT SUM(h.vl_honorario) as vl_honorario, h.id_usuario 
FROM empresa as h 
INNER JOIN honorario as p 

-- sua relação
ON p.id_honorario = h.id_honorario

WHERE h.id_empresa = 86 
-- isso aqui filtra todos os meses = 12
-- ou seja só tem 2 meses como você mostrou na pergunta
AND MONTH(h.dt_honorario) = 12 
and p.tp_honorario = 'Mensal' 
GROUP by h.id_usuario

To do what you described;

  

I'm trying to group the vl_honorario Fee Value if the date of the   is equal to month 12 or if the Type of Honor tp_honorario =   'Monthly'. Sql looks like this:

Your filter p.tp_honorario = 'Mensal' must be used as a OR . That is, 

  AND (MONTH(h.dt_honorario) = 12 or p.tp_honorario = 'Mensal')
    
02.12.2016 / 12:27
Laravel - How to call a different action from store, update, edit? LinearLayout dimensions, defined in java, do not maintain proportionality between resolutions