hi,
I'm using a calculated field in order to show the number of children a record has.
I have 2 tables:
- Parent: f_cont_improv_nonconformities
- Child: f_cont_improv_remedial_action
I'm using this query:
SELECT COUNT(*) FROM `f_cont_improv_nonconformities`
LEFT JOIN `f_cont_improv_remedial_action` ON `f_cont_improv_remedial_action`.`nonconformity_id`=`f_cont_improv_nonconformities`.`ID`
WHERE `f_cont_improv_nonconformities`.`ID`='%ID%'
the result is that when the parent has one or more children the query drops the correct result but... when there is no children... the query drops always the result: 1.
Any idea?
Problem with calculated fields
Re: Problem with calculated fields
Hi,
I've been investigating a llitle bit.
My query always return a joined record where the part coming from the child table includes the ID with the null value.
I'm not an SQL expert but I think this join shouldn't include these cases.
So, I've modified my quey including the prevention for null values
SELECT COUNT(*) FROM `f_cont_improv_nonconformities`
LEFT JOIN `f_cont_improv_remedial_action` ON `f_cont_improv_remedial_action`.`nonconformity_id`=`f_cont_improv_nonconformities`.`ID`
WHERE `f_cont_improv_nonconformities`.`ID`='%ID%'
AND `f_cont_improv_remedial_action`.`ID` IS NOT NULL
Now it works as expected.
Thanks
I've been investigating a llitle bit.
My query always return a joined record where the part coming from the child table includes the ID with the null value.
I'm not an SQL expert but I think this join shouldn't include these cases.
So, I've modified my quey including the prevention for null values
SELECT COUNT(*) FROM `f_cont_improv_nonconformities`
LEFT JOIN `f_cont_improv_remedial_action` ON `f_cont_improv_remedial_action`.`nonconformity_id`=`f_cont_improv_nonconformities`.`ID`
WHERE `f_cont_improv_nonconformities`.`ID`='%ID%'
AND `f_cont_improv_remedial_action`.`ID` IS NOT NULL
Now it works as expected.
Thanks
Re: Problem with calculated fields
Maybe you should use an INNER JOIN instead of a Left Join?
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Problem with calculated fields
I'll try!!
Thanks
Thanks
Re: Problem with calculated fields
It works perfectly
Thanks!!
Thanks!!