Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 11th, 2013, 01: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
Reply With Quote
  #2 (permalink)  
Old February 11th, 2013, 10:39 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,145, Level: 23
Points: 3,145, Level: 23 Points: 3,145, Level: 23 Points: 3,145, Level: 23
Activity: 50%
Activity: 50% Activity: 50% Activity: 50%
 
Join Date: Jan 2006
Location: , , .
Posts: 641
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!)
Reply With Quote
  #3 (permalink)  
Old February 11th, 2013, 12: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.
Reply With Quote
  #4 (permalink)  
Old February 11th, 2013, 02:03 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,145, Level: 23
Points: 3,145, Level: 23 Points: 3,145, Level: 23 Points: 3,145, Level: 23
Activity: 50%
Activity: 50% Activity: 50% Activity: 50%
 
Join Date: Jan 2006
Location: , , .
Posts: 641
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!)
Reply With Quote
  #5 (permalink)  
Old February 12th, 2013, 08: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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 08: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



All times are GMT -4. The time now is 10:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.