CODEWITHSUNDEEP
sqlsql-server
jfritts6524
jfritts6524

Reputation: 77

SELECT statement that only shows rows where there is a NULL in a specific column

I've got an issue I've been racking my brain on this and the code I have makes sense to me but still doesn't work.

Here is the question:

Give me a list of the names of all the unused (potential) caretakers and the names and types of all unclaimed pieces of art (art that does not yet have a caretaker).

Here is how the tables are set up:

Here is the code I have:

SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    Donations.CareTakerID = ''

Any help would be very much appreciated!

Upvotes: 0

Views: 408

Answers (4)

rwking
rwking

Reputation: 1032

I would suggest two queries for the reasons I noted in my comment on the OP above... However, since you requested one query, the following should get you what you asked for, although the result sets are not depicted side-by-side.

SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
LEFT JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    NULLIF(Donations.CareTakerID,'') IS NULL
UNION    -- Returns a stacked result set
SELECT 
    CareTakerName, ArtName, ArtType
FROM 
    CareTakers 
RIGHT JOIN 
    Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE 
    NULLIF(CareTakers.CareTakerID,'') IS NULL

If this is not sufficient, I can supply two separate queries as I suggested above.

*EDIT: Included NULLIF with '' criteria to treat blank and NULL equally in the where clause.

Upvotes: 2

Orlando Herrera
Orlando Herrera

Reputation: 3531

Firstly, You need to know What is a NULL value. Is it zero, blank space or something else? The answer is: No.

NULL is not a value, it only means that a value wasn't provided when the row was created.

SELECT d.ArtName, d.ArtType
,(SELECT CareTakerName FROM CareTakers c WHERE c.CareTakerID = d.CareTakerID)CareTakerName
FROM Donations d
WHERE ISNULL(d.CareTakerID, 0) = 0

*I like to use a "default" value for a NULL column

More infotmation here: SQL NULL Values

Upvotes: 0

Duston
Duston

Reputation: 1641

Donations.CareTakerID = '' is not the same as testing for NULL. That's testing for an empty string.

You want

Donations.CareTakerID is NULL

Also note that 

Donations.CaretakerID = NULL

will not give you what you want either (a common mistake.)

Upvotes: 1

Ricardo Peres
Ricardo Peres

Reputation: 14525

Use a LEFT JOIN:

SELECT CareTakerName, ArtName, ArtType
FROM CareTakers
LEFT JOIN Donations ON CareTakers.CareTakerID = Donations.CareTakerID
WHERE Donations.CareTakerID IS NULL

Upvotes: 1

Related Questions