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
Second, one method uses
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.
- 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.