CODEWITHSUNDEEP
phpmysql
Tony D
Tony D

Reputation: 25

where and having precedence in mysql query

I have been having problems with this query for close to 3 hours and no amount of googling is helping me so far:

select id,nombres,apaterno,amaterno, (select sum(cargo) from tb_consultorios_recibos_transacciones where id_paciente = tb_consultorios_pacientes.id and date(fecha_trans)>='2015-03-01' and date(fecha_trans)<='2015-03-31') as cargospaciente, (select sum(abono) from tb_consultorios_recibos_transacciones where id_paciente = tb_consultorios_pacientes.id and date(fecha_trans)>='2015-03-01' and date(fecha_trans)<='2015-03-31') as abonospaciente from tb_consultorios_pacientes where id_consultorio = 3 order by apaterno asc, cargospaciente desc

besides the where clause, I would like only to display rows where the alias cargospaciente or abonospaciente are greater than 0, this is the query I am trying which is obviously not working:

select id,nombres,apaterno,amaterno, (select sum(cargo) from tb_consultorios_recibos_transacciones where id_paciente = tb_consultorios_pacientes.id and date(fecha_trans)>='2015-03-01' and date(fecha_trans)<='2015-03-31') as cargospaciente, (select sum(abono) from tb_consultorios_recibos_transacciones where id_paciente = tb_consultorios_pacientes.id and date(fecha_trans)>='2015-03-01' and date(fecha_trans)<='2015-03-31') as abonospaciente from tb_consultorios_pacientes where id_consultorio = 3 having (cargospaciente>0 or abonospaciente>0)  order by apaterno asc, cargospaciente desc

any help on how to specify having and where in the same clause?

Upvotes: 0

Views: 53

Answers (1)

Rahul
Rahul

Reputation: 77876

Try it like below rather; by getting the sum of required column with proper grouping and then join that result set with outer select result

select tcp.id,
tcp.nombres,
tcp.apaterno,
tcp.amaterno, 
tab.sum_cargo,
tab.sum_abono
from tb_consultorios_pacientes  tcp
join
(
select id_paciente, sum(cargo) as sum_cargo, sum(abono) as sum_abono
from tb_consultorios_recibos_transacciones 
where date(fecha_trans)>='2015-03-01' 
and date(fecha_trans)<='2015-03-31'
group by id_paciente
having sum_cargo > 0 or sum_abono > 0
) tab
on tcp.id = tab.id_paciente
where tcp.id_consultorio = 3 
order by tcp.apaterno asc, tcp.cargospaciente desc

Upvotes: 1

Related Questions