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

AFAIK there is no inner left join:
https://www.postgresql.org/docs/10/sql-select.html#SQL-FROM
"
join_type

One of

[ INNER ] JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

FULL [ OUTER ] JOIN

CROSS JOIN
"

> and learn where to specify no nulls.

You can't it is the nature of the join:

"LEFT OUTER JOIN returns all rows in the qualified Cartesian product
(i.e., all combined rows that pass its join condition), plus one copy of
each row in the left-hand table for which there was no right-hand row
that passed the join condition. This left-hand row is extended to the
full width of the joined table by inserting null values for the
right-hand columns. Note that only the JOIN clause's own condition is
considered while deciding which rows have matches. Outer conditions are
applied afterwards."

What you are seeing are records for which there is a person in the
people table that either:

1) Do not have a record under that person_id in the activities table

or

2) Do not have a record that meets the date filtering you are doing on
next_contact.

>
> Thanks,
>
> Rich
>
>

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