Re: Refining query statement

On 1/17/19 8:14 AM, Rich Shepard wrote:
> On Tue, 15 Jan 2019, Thomas Kellerer wrote:
>
>>    select distinct on (C.contact_id) C.contact_id, C.lname, C.fname,
>> C.direct_phone, O.org_name, A.next_contact
>>    from Contacts as C
>>      join Organizations as O on C.org_id = O.org_id
>>      join Activities as A on C.contact_id = A.contact_id
>>    where A.next_contact <= 'today'
>>      and A.next_contact > '2018-12-31'
>>    order by c.contact_id, a.next_contact DESC;
>
> Combining this with Adrian's advice to use BETWEEN I have this statement
> that almost works:
>
> SELECT DISTINCT ON (c.contact_id) c.contact_id, c.lname, c.fname,
> c.direct_phone, o.org_name, a.next_contact
> FROM Contacts AS c
>   JOIN Organizations AS o ON c.org_id = o.org_id
>   JOIN Activities AS a ON c.contact_id = a.contact_id WHERE
> next_contact BETWEEN '01/01/2019'::date AND 'today'::date ORDER BY
> c.contact_id, a.next_contact DESC;
>
> It fails when the most recent next_contact column in Activities is NULL and
> an earier row has a non-NULL date in the specified range.
>
> I tried specifying max(a.next_contact) and added GROUP BY, but the result
> set all returned o.org_name columns to the same one.
>
> The WHERE clause needs to exclude a contact_id where the most current
> row in
> Activities has NULL for the next_contact column. I've tried a few ideas but
> none work so I need to learn the proper syntax, and I don't find that in
> Rick van der Lans' or Joe Celko's books I have.

Got to thinking more and realized the answer depends on what you want
the query to produce. Can you let us know what is you are trying to pull
out with the query?

>
> Looking forward to learning,
>
> Rich
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com