mySQL query writing problem - MySQL

[JackM]

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.

[Jerry Stuckle]

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.
[email]jstucklexattglobal.net[/email]
==================

[JackM]

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.