On Wed, 13 Feb 2019, Andrew Gierth wrote:
> Adrian> Close to your last posted query. person_id 2 and 3 have NULL
> Adrian> values for activities data as there is no record for 2 and 3 is
> Adrian> out of the date range.:
> DISTINCT ON with no matching ORDER BY at the _same_ query level is
> non-deterministic.
>
> Also DISTINCT ON isn't efficient. Consider instead something along the
> lines of:
Andrew/Adrian,
I again read about DISTINCT and DISTINCT ON and fully understand them. I've
also again read about JOINs; I understand them in terms of sets and _think_
that in this query the people table is the LEFT (many) while the
organizations and activities tables are the RIGHT (one) in the many-to-one
relationships. That is, for each person_id there is only one org_id and only
one next_contact that meets the three constraints.
I'm now working on understanding how the syntax in the examples you two,
Ken, and others have provided expresses the many-to-one relationships of
organization and activities to people. I have the syntax that returns the
next_date meeting the WHERE constraints to each person_id and am now
focusing on adding the additional people and organization columns to the
results. Might not be until tomorrow or Friday but I'll let you and the list
subscribes know when I have understood all your suggestions and get the
results I want from the query.
Thanks again,
Rich