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@xxxxxxxxxxx
