IFNULL returning NULL

[Adamhumbug]

Hi All,

I have the following, and when i run it the ifnull() is returning null rather than 0 as shown in the attached.

Any help on this would be greatly appreciated.

select *
from 
( SELECT ptsl_ptd_id, SUBSTRING(ptsl_date,1,10) as ptsl_date, ptsl_z_id, z_rfid, ptsl_limit 
	FROM `prs_ptsl` 
	inner join prs_z on ptsl_z_id=z_id 
	where ptsl_ptd_id='7' 
) as limits 
left join 
( SELECT pr_ptd_id, za_sdate, za_z_id, za_z_rfid, IFNULL(count(za_pr_id), 0)
	as used 
	FROM `prs_za` 
	inner join prs_pr on za_pr_id=pr_id 
        where prs_pr.pr_status = 'Approved' or prs_pr.pr_status = 'Submitted'
	group by za_sdate, za_z_id, za_z_rfid 
	) 
as used 
on limits.ptsl_ptd_id=used.pr_ptd_id 
and limits.ptsl_date=used.za_sdate 
and ptsl_z_id=za_z_id 
where ptsl_date = '2021-06-12' and (ptsl_limit - IFNULL(used, 0) >= 0)
limit 100

 

[Barand]

I can't see your data so I don't know which rows match, but if you have a LEFT JOIN, and there is no match, then the used.* will be NULL, regardless of your uses of IFNULL() in the subquery.

You need the IFNULL() in the top level select (instead of "SELECT * ") to fix.

[Adamhumbug]

Thanks so much for your reply as always!

Will the IFNULL be used in the maths at the bottom of this query?

(ptsl_limit - IFNULL(used, 0)

 

[Barand]

The "WHERE ptsl_date = '2021-06-12' " is OK in the WHERE clause but you cannot put conditions on a LEFT JOINed subquery (or table) in a WHERE clause, they need to be in the join's ON clause.