SQL question

BrianK · 04-12-2007, 04:18 PM

In my table, I have a blob field called "order_comments" The following query produces the following output:

Code:

select orders_supplement_id, shipping, order_comments from orders_supplement, orders where orders_supplement_id > 110000 AND order_id = orders_supplement_id AND shipping not like "%US%" limit 10;
+----------------------+----------------+-----------------------+
| orders_supplement_id | shipping       | order_comments        |
+----------------------+----------------+-----------------------+
|               110007 | Express-Canada | NULL                  | 
|               110050 | Canada         | You have nice things! | 
|               110055 | Intl           | NULL                  | 
|               110081 | Canada         | NULL                  | 
|               110082 | Canada         | NULL                  | 
|               110101 | Canada         | NULL                  | 
|               110107 | Canada         | NULL                  | 
|               110119 | Intl           | NULL                  | 
|               110126 | Canada         | NULL                  | 
|               110147 | Canada         | NULL                  | 
+----------------------+----------------+-----------------------+
10 rows in set (0.00 sec)

So now, I want to pick only those results that have something in the comment field, but I can't seem to get it to work. I've tried:

order_comments is not null
order_comments not like "NULL%"

... and a few other ridiculous incarnations, but I can't seem to get anything other than the results above. ideas?

zaichik · 04-13-2007, 08:13 AM

Hello Brian,

IS NOT NULL ought to do it. Strange.

Code:

mysql> describe test;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| pk       | int(11) |      |     | 0       |       |
| my_field | blob    | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test;
+----+----------+
| pk | my_field |
+----+----------+
|  1 | NULL     |
|  2 | hi       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from test where my_field is not NULL;
+----+----------+
| pk | my_field |
+----+----------+
|  2 | hi       |
+----+----------+
1 row in set (0.00 sec)

mysql>

Are you getting an error message? Is the problem perhaps elsewhere in the query? NULL is a special value and a reserved keyword in standard SQL, so you do not want to put quotes around it (or you will end up matching the string 'NULL', which is a different beast altogether, of course).

If you could post the entire query along with the results, as well as which RDBMS you are using, that might help.

haydari · 04-13-2007, 10:11 AM

And also null is not equal to null so you can't compare or check them in this way. As mentioned is NULL and is not NULL have to be the proper command.