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