jmigdelacruz jmigdelacruz - 9 days ago 6
MySQL Question

MySQL - Using where not exists returns null

finally signed up because i cant seem to get why a simple query of mine keeps returning null.

SELECT CardNo FROM BOOK_LOANS WHERE DueDate=DateReturned AND
NOT EXISTS
(SELECT CardNo FROM BOOK_LOANS WHERE DueDate<>DateReturned)


I've been trying to find use cases of
WHERE NOT EXISTS
but cant seem to find any using
WHERE AND NOT EXISTS
. Is this the right way to go about this?

My query works when i instead use
NOT IN
but i have to use
NOT EXISTS
in this query

Answer

Try the following query:

SELECT CardNo FROM BOOK_LOANS A WHERE A.DueDate=A.DateReturned AND
NOT EXISTS
(SELECT CardNo FROM BOOK_LOANS B WHERE B.CardNo = A.CardNo B.DueDate<>B.DateReturned)

Basically, you need to join the outer query with inner query on CardNo otherwise NOT EXISTS will return any record where dates do not match.

Comments