SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this a too simple query

    Hi all, I'm trying to get a result from a simple query. Unfortunatly the result did not come.

    Here is the problem:

    Say I have a table with 20 records, each of those 4 queries should return 5 records, but only 1 did. 5 records had no value for field1, 5 records had no value for field2, 5 records had no value for field1 and field2 and finally 5 records had value in field1 and field2.

    select * from table where field1 = ''
    select * from table where field2 = ''
    select * from table where field1 = '' and field2 = ''
    select * from table where field2 <> ''

    The last query return me 5 records. All other queries return 0 row no error ???

    Note:field1 and field2 are of type varchar

    What did I miss I'm clue less

    Thanks to anyone that can help!

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really need to see the table and if possible the data...
    Aaron Brazell
    Technosailor



  3. #3
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As Sketch already mentioned it would be necessary to see table and data.
    My guess is there's a (default) value for those fields,
    therefor they are not empty and you get a result only with checking them <> ''
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  4. #4
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the table is created from a php script and declared as following.

    field1 varchar(50) null,
    field2 varchar(50) null

    There is no default value, but you open my mind when you talk about default value. The following query works

    select * from table where (field1 is null or field1 = '') and (field2 is null or field2 = '')

    Thanks so much guys!

  5. #5
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, there's a difference between NUL(L), 0 and empty
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •