Hello Mr. Stephens,

I will kindly ask you a question about a simple query. I would like to understand why is behaving the way it does:

SELECT
j.title
FROM
jobs AS j
WHERE
j.expire > NOW() AND
j.suspended IS NULL

expire and suspended are both "datetime".

It will collect all the jobs, including the ones that are marked "suspended" (have a datetime).

If I add 'j.suspended' as here:
SELECT
j.title,
j.suspended
FROM
jobs AS j
WHERE
j.expire > NOW() AND
j.suspended IS NULL

It will return NULL for all fields of the "suspended" column, even if some entries are not NULL (those that are marked suspended by a datetime entry should be not null).

Any way to overcome this situation? My intention is clear to display just those jobs that did not expired and are not suspended.

Thank you

The AND in the query means it should return records where both conditions are true: j.expire > NOW() and j.suspended IS NULL. That means all of the records returned have j.suspended is null.

I think that's what you're doing here. You're getting jobs where expire is after now and suspended is null.

If that's not what you want, can you explain in a little more detail what you want? I may not understand.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

I guess I oversimplified the example and no issues arise from my previous example. Please let me try again:

states
------
abrv
name
population
surface

jobs
----
jobid
title
abrv
expire
suspended

SELECT
j.title,
s.name,
j.suspended
FROM
jobs AS j
INNER JOIN state AS s ON j.abrv = s.abrv
WHERE
j.expire > NOW() AND
j.suspended IS NULL

One job is listed in several states. This query returns "suspended" (NOT NULL) records.

You might double-check the data and make sure two states don't use the same abbreviation. I don't see why it would return a job in multiple states otherwise.

I don't see why it would return records with suspended NOT NULL. Sorry but I don't see why it would do that.

Can you show the data for the smallest number of records that you can get to demonstrate the problem? I don't see what;s wrong with your query but maybe if I see the data I'll be able to tell.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

To be honest I do not see why it doesn't work either. With a small number of records works as expected, with a great number of records it begun to return crazy stuff. This is a development mysql database, have you ever encounter such behavior?

I can certainly provide you access to the machine if you are intrigued about this issue, but I would prefer to drop an email as I cannot post the public details on the board.

Thank you for your help, I will look into it further and if I discover what went wrong I will update the thread.