SEO

#1 (**permalink**) 02-28-2008, 07:56 AM

I have a table with a dozen or so fields. I am trying to return all 12
fields in all records that contain an entry in the field named 'week'.
This select does that:

"SELECT * FROM foo WHERE NOT ISNULL(week) ORDER BY week DESC";

but it also includes all other records that do not have an entry in the
week field after the DESC ones have been listed. How can I modify the
query to only return the ones that have an entry in week and none of the
other ones? I thought NOT ISNULL would take care of it but it doesn't.

#2 (**permalink**) 02-28-2008, 07:56 AM

JackM wrote:
> I have a table with a dozen or so fields. I am trying to return all 12
> fields in all records that contain an entry in the field named 'week'.
> This select does that:
>
> "SELECT * FROM foo WHERE NOT ISNULL(week) ORDER BY week DESC";
>
> but it also includes all other records that do not have an entry in the
> week field after the DESC ones have been listed. How can I modify the
> query to only return the ones that have an entry in week and none of the
> other ones? I thought NOT ISNULL would take care of it but it doesn't.

The fields probably do contain information - like an empty string. But
not knowing how week is defined, it's hard to say what might be in there.

Remember - an empty string is a value, whereas NULL is specifically the
lack of a value.

If this is a varchar field, you can check with something like:

"SELECT * FROM foo WHERE NOT ISNULL(week) AND week<>'' ORDER BY week DESC.

Or you can correct your database to replace the empty strings with nulls.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 02-28-2008, 07:56 AM

Jerry Stuckle wrote:
> JackM wrote:
>
>> I have a table with a dozen or so fields. I am trying to return all 12
>> fields in all records that contain an entry in the field named 'week'.
>> This select does that:
>>
>> "SELECT * FROM foo WHERE NOT ISNULL(week) ORDER BY week DESC";
>>
>> but it also includes all other records that do not have an entry in
>> the week field after the DESC ones have been listed. How can I modify
>> the query to only return the ones that have an entry in week and none
>> of the other ones? I thought NOT ISNULL would take care of it but it
>> doesn't.
>
>
> The fields probably do contain information - like an empty string. But
> not knowing how week is defined, it's hard to say what might be in there.
>
> Remember - an empty string is a value, whereas NULL is specifically the
> lack of a value.
>
> If this is a varchar field, you can check with something like:
>
> "SELECT * FROM foo WHERE NOT ISNULL(week) AND week<>'' ORDER BY week DESC.
>
> Or you can correct your database to replace the empty strings with nulls.
>

Thanks Jerry. The field is varchar and hd been defaulted to NULL so your
suggestion worked perfectly.

But in the interest of furthering my query structure education, does
that select you wrote translate to something approximating "AND week is
less than or greater than empty"? Just trying to understand how the <>
works in the statement.

#4 (**permalink**) 02-28-2008, 07:56 AM

On Sat, 15 Jul 2006 22:18:32 -0400, JackM wrote:
> Thanks Jerry. The field is varchar and hd been defaulted to NULL so your
> suggestion worked perfectly.
>
> But in the interest of furthering my query structure education, does
> that select you wrote translate to something approximating "AND week is
> less than or greater than empty"? Just trying to understand how the <>
> works in the statement.

Essentially. It behaves like "not equal". Except with regard to NULL,
for the usual NULL reasons. (See the manual for those.)

--
1. My Legions of Terror will have helmets with clear plexiglass visors,
not face-concealing ones.
--Peter Anspach's list of things to do as an Evil Overlord

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

mySQL query writing problem