CODEWITHSUNDEEP
mysql
jmigdelacruz
jmigdelacruz

Reputation: 39

MySQL - Using where not exists returns null

I'm trying to figure out why a simple query returns null:

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

I've been learning about WHERE NOT EXISTS but cant seem to find any examples using both WHERE and NOT EXISTS. Is this there a better way to go about this?

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

Upvotes: 1

Views: 1314

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

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.

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

When you use NOT EXISTS clause you must link subquery with the main query.

An example:

SELECT *
FROM persons p
WHERE NOT EXISTS(SELECT 'OTHER'
FROM persons p2
WHERE p.id <> p2.id AND p.city_id = p2.city_id)

In this query I want to extract all persons where not exists another person in the same city. So I linked my subquery table (persons p2) with main query table (persons p)

In your example, surely, exist two records with DueDate<>DateReturned so your query returns always NULL

Upvotes: 0

Related Questions