Properly, NULL values should be matched with 'foo IS NULL', as opposed to 'foo = NULL' which, by standard definition, always returns false regardless of the value of foo
- michael dykman On Mon, 2005-02-28 at 16:02, Rob Brooks wrote: > <correction> in question below, the problem is not in record '7047' but in > the record which starts with the name 'Triad' > > also ... I'm using 4.0.20-standard-log > > -----Original Message----- > From: Rob Brooks [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 2:56 PM > To: mysql@lists.mysql.com > Subject: SQL help > > Can someone help me with this? > > this statement: > > select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left > join items_online on items_online.ItemKey = Items.ID where (Name regexp > 'ad') and AccountKey = 108 and Items.Active = 1; > > gives this: > > > +-----------------------------------+--------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------+------+------+ > | Name | Detail > | ID | ID | > +-----------------------------------+--------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------+------+------+ > | Jade Arch Series | 3/4" thick beveled jade acrylic arch > on a beveled jade acrylic base > | 7015 | 437 | > | Jade Arrow Series | 3/4" thick beveled jade acrylic arrow > on beveled jade acrylic base. > | 7016 | 438 | > | Queen Jade | Unique and elegant shape makes a > beautiful free standing award. Glass is jade color. > | 7041 | 463 | > | Octavia Jade | Beautiful jade glass octagon shaped > award. > | 7043 | 465 | > | Jade Arresting Obelisk | Pristine, monumental style jade glass > award. > | 7045 | 467 | > | Jade Autumn Leaf | Contemporary design derived from > natural shape. Cut from jade glass. > | 7047 | 469 | > | Triad | Free standing clear acrylic with a > thick triangular shape. > | 7069 | NULL | > | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, > black, or light velour backgrounds. Retro style clock with new style design > that allows it to hang in vertical or horizontal position. Large engravable > black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | > | Traditional Wood and Glass Clock | Traditional clock with polished brass > and cherry wood finished accents. Polished glass upright holds the clock. > Black brass engravable plate. Lifetime Guaranteed Quartz Movement > | 7087 | 508 | > > < ... truncated for brevity .... > > +-----------------------------------+--------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------+------+------+ > 14 rows in set (0.06 sec) > > > but this statement: > > select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left > join items_online on items_online.ItemKey = Items.ID where items_online.ID = > NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; > > > gives this: > > Empty set (0.00 sec) > > The only difference in the 2 statements is the 'where items_online.ID = > NULL' part. > Clearly in the first set, items_online.ID = NULL in record 7047 but when I > look for it specifically, it is not found?? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
