[RESOLVED] nothing returned... select query wrong?

[freebird1127]

I am stumped. I have a table named csfile which includes a column named email. Some records in the table have a value for email in place, and others have nothing in the email field. The query:
SELECT * FROM csfile
returns all records in the table. The query:
SELECT * FROM csfile WHERE email='email@domain.com'
returns all records in the table where the email field is email@domain.com. The query:
SELECT * FROM csfile WHERE email<>'email@domain.com'
returns nothing! Records do exist that exhibit this condition... does anyone have some advice?

Thanks,
Evan

[chazzy]

What if you use != instead of <>?

[freebird1127]

Negative. This works for returning other values, but still does not return the record when the email field is empty ('') (not null). Thanks though...

[chazzy]

what dbms?

[freebird1127]

mysql

[freebird1127]

A friend figure this out. Correct syntax is

SELECT * FROM csfile WHERE email IS null

[chazzy]

Hmmm.

I don't see why it's not working for you. what kind of column is email? Are you trying to say that you're expecting the rows where the column value is null to come back?

For example, I have this dummy data:


mysql> select * from users where emailAddress != 'email@somedomain.com';
+----+--------------------------+---------------------+-----------+---------------------+------------+----------------+
| id | password | creationDate | username | emailAddress | profile_id | user_status_id |
+----+--------------------------+---------------------+-----------+---------------------+------------+----------------+
| 1 | ZqZKRG3f+n3mOiLgmcKnQQ== | 2008-03-23 19:58:51 | JohnAment | my@email.com | 1 | NULL |

| 2 | abc | 2008-04-02 07:40:24 | sammy | | NULL | NULL |

+----+--------------------------+---------------------+-----------+---------------------+------------+----------------+
2 rows in set (0.00 sec)


And if I do a check against the email:


mysql> select * from users where emailAddress != 'my@email.com';
+----+----------+---------------------+----------+--------------+------------+----------------+
| id | password | creationDate | username | emailAddress | profile_id | user_status_id |
+----+----------+---------------------+----------+--------------+------------+----------------+
| 2 | abc | 2008-04-02 07:40:24 | sammy | | NULL | NULL |
+----+----------+---------------------+----------+--------------+------------+----------------+
1 row in set (0.00 sec)


What version are you using?

[chazzy]

A friend figure this out. Correct syntax is

SELECT * FROM csfile WHERE email IS null

In your previous post, you said that the email field is blank ('') not null.

but still does not return the record when the email field is empty ('') (not null).

[freebird1127]

Sorry, to the best of my knowledge, it isnt null. If a varchar field contains data, then the data is replaced with '', is it null? I'm not sure, but the SQL statement my friend suggested worked.