Join bringing wrong value

Question

I have two tables

Enterprise:

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

Honorary:

id_honorario   tp_honorario  
   5             Mensal                 
   6             Anual
   7             Trimestral

I’m trying to group the Honorary Fee vl_honorario if the fee date is equal to month 12 or if the Fee Type tp_honorario = 'Monthly'. A sql is 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 get back to 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 because the fee type is Monthly

vl_honorario  id_usuario  
    1100          1                 
    300          212

Answer 1

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

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 Honorary Fee amount vl_honorario if the date of fee is equal to month 12 or if the Honorary Fee Type = 'Monthly'. A sql is like this:

It is necessary that your filter p.tp_honorario = 'Mensal' be using as a OR. That is to say,

  AND (MONTH(h.dt_honorario) = 12 or p.tp_honorario = 'Mensal')

Answer 2

You are misusing the INNER JOIN, making the bond for what should be their condition OR:

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