Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 11th, 2013, 02:48 AM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default Problem with NULL

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
 
Old February 11th, 2013, 11:39 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
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).
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.

Quote:
My intention is clear to display just those jobs that did not expired and are not suspended.
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!)
 
Old February 11th, 2013, 01:26 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

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.
 
Old February 11th, 2013, 03:03 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
One job is listed in several states.
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.

Quote:
This query returns "suspended" (NOT NULL) records.
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!)
 
Old February 12th, 2013, 09:34 AM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
SelectImage eventhandler-Null Problem ToddJones BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 3 July 31st, 2009 04:00 AM
problem in displaying null value to the control in the footer template of gridview jazzydonald ASP.NET 3.5 Basics 14 February 16th, 2009 09:33 AM
How to set Not Null constraint to Null Columns arasu Oracle 1 August 22nd, 2005 10:09 AM
Problem trapping not null constraint method Access 3 June 29th, 2005 07:14 AM
Problem searching for NULL values ltdanp21 SQL Language 2 June 16th, 2004 06:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.