In a relationship of People (PPL) > Ethnicities (ETH) > Ethnicity_groups (EGR), I want to do an eSQL query to find the number of People in each Ethnicity_group.
This is where I've got to so far. (PPL::FILTERIDS holds a list of keys from a found set). And the step before this, which is a simpler query of just ethnicities (i.e. a "two level" query) works fine - it's the addition of the third level, EGR, which has caused the problem.
I'm hoping that it's a simple syntax error, as it currently returns "?", but I can't see it. Any pointers much appreciated.
~idList = Substitute ( PPL::FILTERIDS; "¶"; "','");
"SELECT COUNT (*)
FROM PPL p, ETH e, EGR g
WHERE p.id IN ('" & ~idList & "')
AND p.id_ETH = e.id
AND e.id_EGR = g.id
GROUP BY g.group" ;
~result = ExecuteSQL( ~query ; "" ; ""; "" )