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.

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

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

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

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.