Problem with select to show records

Question

Problem with select to show records

Navigation

#1 by (1 votes)

1

I am developing a fixed asset management system and I have a query that brings me information from different tables, one of the tables called kz_sgaf_distribdebajas (distribution of casualties) keeps records about asset losses that have been made to a company. asset acquisition, where I bring the minimum balance of all the losses that have been made to the same asset and show it to me, but I need that when the record with that balance is 0 do not show it to me, I try to place it in the where saldo_adqui > 0 , but it brings me the previous asset low, before having reached 0

output kz_sgaf_distribdebajas

If you realize it, I made two withdrawals for the same asset and in the consultation I have you bring me the minimum balance of the two, but if the balance reaches zero, do not bring it to me.

Entry:

SELECT distinct adq.id_adqui, con.id_activo, con.cod_activo, con.descripcion_activo, emp.nombre_empr, cat.nombre_categ, 
CASE WHEN db.saldo_adqui is null THEN adq.cantidad_adqui ELSE MIN(db.saldo_adqui) END as saldo_restante
FROM kz_sgaf_adquisicionesaf adq
LEFT JOIN kz_sgaf_distribdebajas db ON db.id_adqui = adq.id_adqui
INNER JOIN kz_sgaf_configaf con ON con.id_activo = adq.id_activo
INNER JOIN kz_sgaf_empresas emp ON emp.id_empr = con.id_empr
INNER JOIN kz_sgaf_categorias cat ON cat.id_categ = con.id_categ
group by adq.id_adqui;

Exit:

100 102 12312312    Maquinaria Pesada   Empresa Uno Categoría Uno   0
101 102 12312312    Maquinaria Pesada   Empresa Uno Categoría Uno   5
102 102 12312312    Maquinaria Pesada   Empresa Uno Categoría Uno   500

I need that first record does not come out, since it has a value of 0 and I do not need to show it to the user.

mysql sql

asked by Felipe Toloza 23.02.2017 в 06:55

source

1 answer

Reminders if a cell matches a date in Excel VBA [closed] No Django settings specified [closed]

score 1 · Accepted Answer

A possible solution could be to do a SELECT of that same query and where to filter by saldo_restante > 0. That is:

SELECT * FROM
 (SELECT distinct adq.id_adqui, con.id_activo, con.cod_activo, con.descripcion_activo, emp.nombre_empr, cat.nombre_categ, 
 CASE WHEN db.saldo_adqui is null THEN adq.cantidad_adqui ELSE MIN(db.saldo_adqui) END as saldo_restante
 FROM kz_sgaf_adquisicionesaf adq
 LEFT JOIN kz_sgaf_distribdebajas db ON db.id_adqui = adq.id_adqui
 INNER JOIN kz_sgaf_configaf con ON con.id_activo = adq.id_activo
 INNER JOIN kz_sgaf_empresas emp ON emp.id_empr = con.id_empr
 INNER JOIN kz_sgaf_categorias cat ON cat.id_categ = con.id_categ
 group by adq.id_adqui) as bajas_valorizadas
WHERE saldo_restante > 0