NOT EXIST not returning any rows

Question

I have the following SQL query

select distinct fnamn,enamn,pubdatum
from author,book
where pubdatum <'1961-0-0 AND author.enamn=book.forfattarenamn;

And this is fine it shows all authors who have a book published before 1961 but what if i want only the authors who published before but not after 1961?

I thought about adding a NOT EXISTS like this

select distinct fnamn,enamn,pubdatum
from author,book
where pubdatum <'1961-0-0' AND author.enamn=book.forfattarenamn
AND NOT EXISTS
(select distinct fnamn,enamn,pubdatum
from author,book
where pubdatum >='1961-0-0' AND author.enamn=book.forfattarenamn);

So in the subquery it list all authors who published after 1961. As i understood it this now removes those authors from the orignal query. but running this SQL statement returns no rows. Have i missunderstood the NOT EXISTS?

As it stands, you `NOT EXISTS` is "**no** author has published a book since 1961", not "*this* author has not published a book since 1961". — Damien_The_Unbeliever, Oct 31 '18 at 15:01
why not just between? https://stackoverflow.com/questions/23398632/check-if-current-date-is-between-two-dates-oracle-sql — JavaSheriff, Oct 31 '18 at 15:03
Because it doesn't exclude the authors that have published a book before 1961 and also published a second one after 1961. — Nebi, Oct 31 '18 at 15:07

score 1 · Answer 1 · answered Oct 31 '18 at 15:01

You could do sth like this (pls insert into ID the PK of author):

select distinct a.fnamn,a.enamn,b.pubdatum
from author as a
inner join book as b ON a.enamn=b.forfattarenamn
where b.pubdatum <'1961-0-0'
AND NOT EXISTS
(select 0
from author as aX
inner join book as bX on ax.enamn=bx.forfattarenamn
where bx.pubdatum >='1961-0-0' AND aX.ID = a.ID);

score 0 · Answer 2 · answered Oct 31 '18 at 15:23

What if i want only the authors who published before but not after 1961?

One simple method uses group by and having:

select a.fnamn, a.enamn, max(b.pubdatum)
from author a inner join
     book b 
     on a.enamn = b.forfattarenamn
group by a.fnamn, a.enamn
having max(b.pubdatum) < '1961-01-01';

NOT EXIST not returning any rows

2 Answers2