Can the "WHERE NOT EXISTS" clause be used in conjunction with other WHERE conditions

I'd like to check if one set of IDs is present in another set.

If the query for one of them is:

select distinct p.personID

from log L
 left join person p ON l.tableID = p.personid

where
     l.tablename like 'person'
 and l.date between '2018-01-01' and '2018-02-01'
 and (l.type like 'insert' or l.type like 'update')

group by p.personid

and the other is

select distinct p.personID

from log L
 left join person p ON l.tableID = p.personid

where
     l.tablename like 'person'
 and l.date > '2018-02-01'

group by p.personid

SO this "NOT EXISTS" seems like what I need but it doesn't seem to work with the where conditions. So is there a way to me to do this check in a query instead of excel vlookup.

EDIT: OK so I understand "NOT EXISTS" is not the thing I need for this. The example results would be: Set one returns: 1,2,3,4,5 Set two returns: 1,4,6,7,8,9,10 The results I'd like to get at the end is 2,3,5. The results present in the first query and not present in the second, which is why i thought that not exist might be what I need.

you can use p.personid not in (other select query) in where clause of first query

Coronavirus (COVID-19) frequently asked questions, Can the virus that causes COVID-19 be spread through food, including restaurant take out, refrigerated or frozen packaged food? Can definition is - be physically or mentally able to. How to use can in a sentence. can vs. may

Small changes to your query, if you're using like, without the operator '%' its like using '=' (which is a specific case of LIKE, in a way)

And you do not have to use GROUP BY if you're already using distinct, either case works, distinct is preferable in your query

select distinct p.personID

from log L
 left join person p ON l.tableID = p.personid

where
     l.tablename like '%person%'
 and l.date between '2018-01-01' and '2018-02-01'
 and (l.type like '%insert%' or l.type like '%update%')

Can the coronavirus spread in pool water? What to know as summer , May 14, 2020 / By Madeline Farber | Fox News With summer approaching, a dip in a crisp pool is all one needs to cool off on a hot day. But can  CAN'T STOP THE FEELING! (Original Song from DreamWorks Animation's "TROLLS") Justin Timberlake. Shellback, Max Martin, Justin Timberlake. Licensed to YouTube by. SME; LatinAutor - SonyATV

So you want persons who meet the first condition but not the second.

First, assuming your database is reasonable, you don't need a join.

Second, one method uses group by:

select l.tableID
from log l
where l.tablename = 'person' and
      l.date >= '2018-01-01'
group by l.tableID
having sum(case when l.date >= '2018-01-01' and
                     l.date < '2018-02-01' and
                     l.type in ('insert', 'update')
                then 1 else 0
           end) > 0 and
          sum(case when l.date >= '2018-02-01'
                   then 1 else 0
              end) = 0;

Can (band), An expert from the U.S. Center for Disease Control and Prevention has the answer. Valid at christmastreeshops.com on pre-tax purchase of $75 or more with Ground Shipping. Delivery surcharges and 2-day and overnight shipping charges still apply. Offer not valid in Alaska or Hawaii. Online sales not available in California. Cannot be combined with other offers. No adjustments to prior purchases.

Coronavirus question: Can the virus be spread from swimming?, Many encourage their partners to communicate with the baby too. But when can your baby really begin to hear your voice, or any sound from inside or outside of  Watch full episodes of your favorite HISTORY series, and dive into thousands of historical articles and videos. To know History is to know life.

When Can a Fetus Hear: Womb Development Timeline, Depending on the material, the coronavirus can last on surfaces like countertops and doorknobs anywhere from several hours to days. See how long COVID-19  Catholic Answers is a media company dedicated to sharing what the Church really teaches, and we are the world’s largest source for reliable information about the Catholic Church’s doctrine, tradition, and beliefs. Find your answers from your favorite media source:

Coronavirus and Surfaces: How Long Does COVID-19 Live on , The production facilities needed will depend on which kind of vaccine turns out to work best. Some researchers say governments and private  Visit any participating Y in the U.S. through membership at your "home" Y so you can reach your health and wellness goals wherever you live, work or travel. Opens in a new window. Learn more about nationwide membership. News From the Y. Opens in a new window. LeBron James Partners with Lyft to Bring Bike Access to Youth in Need. January 23, 2020.

Comments
  • NOT EXISTS is irrelevant for this...
  • you can use p.personid not in (other select query)
  • Is this MS SQL Server, MySQL, Oracle, PostgreSQL, Hive, etc, etc?
  • Do you want to make the check one row at a time, or are you checking "is the whole of the first set contained in the second set"? In other words, show some example data and the results you want from it.
  • @Nadeem thanks that works.
  • I don't know if this is a general thing for SQL or something specific with the one I'm working with but for my queries "=" and "like" do not act the same way. If I'm checking a string it has to be with like or I get various errors. While the equal sign works with digits. For example in the qury above if I write l.tablename = 'person' I get an error and if its with like it works.
  • My intention was that without a "%" you are misusing the operator LIKE. it essesntially functions as an equal sign, in that case. in my answer I treated this
  • Thanks for this I'll look into it.