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.klaver@aklaver.com