-2

I have this code runninh:

SELECT IDLOTE,
       SUM(CASE WHEN FECHAMOVTO='2020-01-01'
                THEN cargo
                ELSE 0
                END) *4 as saldo,
       SUM(CASE WHEN FECHAMOVTO>'2019-12-31' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23 or idconcepto=24)
                THEN cargo
                ELSE 0
                END) as saldo2,
       SUM(CASE WHEN FECHAMOVTO<='2019-12-31'  
                THEN cargo-abono
                ELSE 0
                END) as saldo3,
        SUM(CASE WHEN FECHAMOVTO<'2020-01-01' 
                THEN cargo-abono
                ELSE 0
                END) as saldo4,
        SUM(CASE WHEN FECHAMOVTO>='2020-01-01' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23)
                THEN cargo
                ELSE 0
                END) as saldo5    
FROM polizamovtos 
GROUP BY IDLOTE
HAVING SUM(cargo) > SUM(abono)

So, i need to add right join on idlote with the table propietarios

I try with this code:

SELECT IDLOTE,
   SUM(CASE WHEN FECHAMOVTO='2020-01-01'
            THEN cargo
            ELSE 0
            END) *4 as saldo,
   SUM(CASE WHEN FECHAMOVTO>'2019-12-31' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23 or idconcepto=24)
            THEN cargo
            ELSE 0
            END) as saldo2,
   SUM(CASE WHEN FECHAMOVTO<='2019-12-31'  
            THEN cargo-abono
            ELSE 0
            END) as saldo3,
    SUM(CASE WHEN FECHAMOVTO<'2020-01-01' 
            THEN cargo-abono
            ELSE 0
            END) as saldo4,
    SUM(CASE WHEN FECHAMOVTO>='2020-01-01' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23)
            THEN cargo
            ELSE 0
            END) as saldo5    
FROM polizamovtos t1
INNER JOIN asignarlotes on asignarlotes.IDLOTE=polizamovtos.idlote
GROUP BY IDLOTE
HAVING SUM(cargo) > SUM(abono)
2
  • which rdms are you using and what is the problem when you simply inner join your table – nbk Aug 15 '20 at 23:26
  • WHEN I TRIED, I RECEIVED THIS ERROR #1052 - La columna: 'IDLOTE' en field list es ambigua – Sergio Rossetti Aug 16 '20 at 0:28
3

With the second table, mysql doesn't know which column to choose, when they have the same name like IDIOTE, so choose one.

I changed the alias for polizamovtos to p and give a to asignarlotes, that makes it easier to read and you know always which table you want to use

SELECT p.IDLOTE,
   SUM(CASE WHEN FECHAMOVTO='2020-01-01'
            THEN cargo
            ELSE 0
            END) *4 as saldo,
   SUM(CASE WHEN FECHAMOVTO>'2019-12-31' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23 or idconcepto=24)
            THEN cargo
            ELSE 0
            END) as saldo2,
   SUM(CASE WHEN FECHAMOVTO<='2019-12-31'  
            THEN cargo-abono
            ELSE 0
            END) as saldo3,
    SUM(CASE WHEN FECHAMOVTO<'2020-01-01' 
            THEN cargo-abono
            ELSE 0
            END) as saldo4,
    SUM(CASE WHEN FECHAMOVTO>='2020-01-01' and (idconcepto=2 or idconcepto=4 or idconcepto=22 or idconcepto=23)
            THEN cargo
            ELSE 0
            END) as saldo5    
FROM polizamovtos p
INNER JOIN asignarlotes a on a.IDLOTE=p.idlote
GROUP BY p.IDLOTE
HAVING SUM(cargo) > SUM(abono)
3
  • it works but i cant see the columns of asignarlotes. The result only shows same columns – Sergio Rossetti Aug 16 '20 at 0:54
  • i actually don't know your tables or what you want to show, but now you can add every column from a to your select, it will showm the value of the join table. to get a better view. make SELECT p.*,a.* FROM.... and then you see what every ro looks like. If my answer help you,. please accept it – nbk Aug 16 '20 at 0:58
  • Thanks man, it works – Sergio Rossetti Aug 16 '20 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.