I'm trying to use eSQL for a simple query, but one involving multiple JOINS, which I haven't used before.
The query will find all People whose "searchableData" matches the user input, and who have attended a session of the type selected by the user. (The context is a spotlight search on a layout of Session Details, including a list of possible attenders.)
In FM relationships, it looks like this:
GUI -> PTI -> PGM -> SSN -> ATT -> PPL
where GUI is the globals table in which the user specifies the Programme Title (PTI). PGM = Programmes, SSN = Sessions, ATT = Attendances, and PPL = People. So if I was doing this with a FM Find, I'd just Find all PPL at the "end" of the relationship, and constrain the Find by the search string entered.
Using my basic knowledge of eExecuteSQL, and some resources, especially Beverley Voth's "Missing FM12 ExecuteSQL reference", I've come up with this:
ExecuteSQL("SELECT P.id FROM PPL p, ATT a, SSN s, PGM pg, GUI gu
WHERE a.id_PPL = p.id
AND a.id_SSN = s.id
AND s.id_PGM = pg.id
AND pg.id_PTI = gu.PTI.ID
AND searchableData LIKE ? " ; "" ; "" ; "%" & SSN::FILTERINPUT.PEOPLE & "%")
... but no joy (no results). So I'm obviously doing something wrong, and I would really appreciate it if anyone can tell me what.