Re: Subquery to select max(date) value

On 2/13/19 7:37 AM, Rich Shepard wrote:
> On Wed, 13 Feb 2019, Adrian Klaver wrote:
>
>> The LEFT JOIN. There are rows in people for which there no records coming
>> from the sub-select on activities, so the row is 'padded' with NULL
>> values
>> for the missing data.
>
> Adrian,
>
> I assume it's the inner left join. I'll trace what's happening at each step
> and learn where to specify no nulls.

create table people(person_id integer, desc_fld varchar);
create table activities(person_id integer, next_contact date);

insert into people values (1, 'contacted'), (2, 'never contacted'), (3,
'out of range'), (4, 'contacted');

insert into activities values (1, '01/31/19'), (3, '11/01/18'), (4,
'02/02/19');

No activities record for person_id =2, activities data set to NULL:

SELECT * FROM people AS p LEFT JOIN activities AS a ON p.person_id =
a.person_id;
person_id | desc_fld | person_id | next_contact
-----------+-----------------+-----------+--------------
1 | contacted | 1 | 2019-01-31
2 | never contacted | NULL | NULL
3 | out of range | 3 | 2018-11-01
4 | contacted | 4 | 2019-02-02

Close to your last posted query. person_id 2 and 3 have NULL values for
activities data as there is no record for 2 and 3 is out of the date range.:

select
p.person_id,
p.desc_fld,
a.next_contact
from
people as p
LEFT JOIN (
SELECT
DISTINCT ON (person_id)
person_id,
next_contact
FROM
activities a
WHERE
a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
a.next_contact is not null
) a USING (person_id)
;

person_id | desc_fld | next_contact
-----------+-----------------+--------------
1 | contacted | 2019-01-31
2 | never contacted | NULL
3 | out of range | NULL
4 | contacted | 2019-02-02
(4 rows)

What I think you want:

select
p.person_id,
p.desc_fld,
a.next_contact
from
people as p
LEFT JOIN (
SELECT
DISTINCT ON (person_id)
person_id,
next_contact
FROM
activities a
WHERE
a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
a.next_contact is not null
) a USING (person_id)
WHERE next_contact is not null;

person_id | desc_fld | next_contact
-----------+-----------+--------------
1 | contacted | 2019-01-31
4 | contacted | 2019-02-02

>
> Thanks,
>
> Rich
>
>

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