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
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.
AND between B.CardNo = A.CardNo B.DueDate<>B.DateReturnedWhen 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
