I have the following tables: empl, sug1, sug2.

empl has employee names, id column. sug1 and sug2 have suggestions tied to the users id from the empl table.

empl
id   name
1    bob smith
2    jack smith
3    barbara jones
4    barbara jones
5    david noodles
6    mark mcmark

sug1
id    UserId    suggestion                  date
1     2         dont run with eyes closed   01/01/2018

sug2
id    UserId   suggestion                   date
1     4        always walk                 01/01/2019
2     6        jump over rope               02/01/2019

I'm trying to achieve two things. 1) give me a unique list of everyone who has a user account in empl, but no entries in the sug tables. For this, i think this is where I need to go -

select distinct name from empl 
where empl.id not in 
(select sug1.UserId from sug1
    UNION select sug2.UserId from sug2 
    UNION select ......
;

2) the second aspect i'm trying to get to is, show me a list of unique users who have no submissions in any of the sug tables over the past ~90 days ( >= '01/01/2019')

1 Answers

0
Gordon Linoff On

Use not exists!

select e.*
from emp e
where not exists (select 1
                  from sug1 s1
                  where s1.userid = e.id
                 ) and
      not exists (select 1
                  from sug2 s2
                  where s2.userid = e.id
                 );

You can add:

and date >= current_date - interval '90 day'

if you want to restrict the time frame.