AnsweredAssumed Answered

eSQL help please, with multiple JOINS?

Question asked by davehob on Apr 14, 2014
Latest reply on Apr 16, 2014 by ave

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.

 

Dave.

Outcomes