[OT] Finding null records in MYSQL

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT] Finding null records in MYSQL

Steven Evans
Good morning,

I am trying to do a search to find all records that match an ID that
also have null values in two fields, but this is causing me issues...is
there a straight forward way to do this?

LP8/OSX Server/MYSQL 4.0.24

SELECT url_main, id FROM images WHERE event_id = "211" AND public_web
is NULL AND keyword is NULL  ORDER BY images.uploaded_on DESC

This just doesn't seem to be working.  Am I missing something?

Thanks!

sqe



Steven Q. Evans
Director of Technological Resources
MSI Productions
(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Finding null records in MYSQL

Bil Corry-3
Steven Q. Evans wrote:

> Good morning,
>
> I am trying to do a search to find all records that match an ID that
> also have null values in two fields, but this is causing me issues...is
> there a straight forward way to do this?
>
> LP8/OSX Server/MYSQL 4.0.24
>
> SELECT url_main, id FROM images WHERE event_id = "211" AND public_web is
> NULL AND keyword is NULL  ORDER BY images.uploaded_on DESC
>
> This just doesn't seem to be working.  Am I missing something?

Are you sure those columns have null values?  Maybe they're empty strings instead?

SELECT url_main, id FROM images WHERE event_id = "211" AND (public_web is NULL OR public_web = "") AND (keyword is NULL OR keyword = "")  ORDER BY images.uploaded_on DESC


- Bil


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Finding null records in MYSQL

Steven Evans
Thanks, Bil.

I'm not sure what that is...but you are 100% correct...that worked like
a charm!

Thanks, as always!

sqe


On Aug 24, 2006, at 11:08 AM, Bil Corry wrote:

> Steven Q. Evans wrote:
>> Good morning,
>> I am trying to do a search to find all records that match an ID that
>> also have null values in two fields, but this is causing me
>> issues...is there a straight forward way to do this?
>> LP8/OSX Server/MYSQL 4.0.24
>> SELECT url_main, id FROM images WHERE event_id = "211" AND public_web
>> is NULL AND keyword is NULL  ORDER BY images.uploaded_on DESC
>> This just doesn't seem to be working.  Am I missing something?
>
> Are you sure those columns have null values?  Maybe they're empty
> strings instead?
>
> SELECT url_main, id FROM images WHERE event_id = "211" AND (public_web
> is NULL OR public_web = "") AND (keyword is NULL OR keyword = "")  
> ORDER BY images.uploaded_on DESC
>
> - Bil
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>
>
Steven Q. Evans
Director of Technological Resources
MSI Productions
(858)344-3351 • (858)348-0629 • (707)215-1032 Fax • [hidden email]


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Finding null records in MYSQL

Bil Corry-3
Steven Q. Evans wrote:
> I'm not sure what that is...but you are 100% correct...that worked like
> a charm!

Well, if a value is NULL then it has no value, if the value is an empty string, then it's a string of length 0.  What you'll find with MySQL is the value will be set to empty strings if you don't allow NULL for the column.  And if you do allow NULL, but use a GUI to "erase" the contents of a column, it'll be an empty string, not a NULL.  So sometimes you'll end up with both NULL and empty strings.  For dates, you have to test for NULL and dates less than 0001-01-01.

Where NULL is most handy is when you're storing say the number of times someone sneezed today.  With NULL, it means you haven't recorded their answer yet or they choose to not answer the question.  Without it, it could be 0 means no sneezes or it could mean they haven't answered the question yet (well, unless you make the default value -1 or something).


- Bil


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage