I’m using eSQL and a virtual list to do some really useful analysis of activities in the charity where I work. But now I’m stuck.
The basic data structure is thus:
PTI > PGM > SSN - a Programme Title (PTI) can have many Programmes (PGM), and a Programme can have many Sessions (SSN).
By using various eSQL queries, I’m populating a virtual list, which gets displayed in a portal. To get, for example, the number of Sessions within a Programme Title, I’m doing this:
And to get the total no. of Programmes within a Programme Title, I’m doing this:
These work fine, so I’m happy that the eSQL syntax is OK. Note that the set of Sessions under scrutiny is held in SSN::FILTERIDS (which has to be converted to a comma-separated list), and the results need to be grouped by Programme Title.
What I need to do now is to get some counts from other related tables. And this is where I’m stuck. Attendances to a Session are held in an Interaction table (INT), which is in turn related to People (PPL). So the data structure of this bit is SSN > INT < PPL (i.e. the INT table is a join table).
And what I need now is, for example, the number of individual People attending a group of Sessions. (Note that this is not the same as the number of Attendances, as a person may have attended more than one Session.) I will then need other counts from the PPL table (no. of disabled people, etc.).
If you can give me any pointers as to how to structure the query, I’d be very grateful. Everything I've tried so far yields the dreaded '?' (but I must admit to a degree of desperate guesswork!).
Thanks for reading,