AppGini Community Forums

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?

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

Maybe you should use an INNER JOIN instead of a Left Join?

I'll try!!

Thanks

It works perfectly

Thanks!!