not in clause broken

[IBazz]

Hi,

Almost at my wits end here

This is my table

CREATE TABLE IF NOT EXISTS booked (
  id int(11) NOT NULL auto_increment,
  business_id int(11) NOT NULL,
  room_number varchar(25) NOT NULL,
  room_type varchar(99) NOT NULL,
  product_name varchar(32) NOT NULL,
  description text NOT NULL,
  check_in_date date NOT NULL,
  check_out_date date NOT NULL,
  actual_occupancy tinyint(4) NOT NULL,
  room_status varchar(16) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY business_id (business_id,room_number,check_in_date)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

and I am running a query which works but for the not in clause

and rp.room_number NOT IN (
              SELECT b.room_number
                 FROM booked b
                 WHERE b.business_id = ?
                 and b.room_number = rp.room_number
                 and check_out_date >= ?   # arrival_requested
                 and check_in_date <= ?    # departure_requested
                                     )

The query still return the results as if the not in clause was omitted. Can the b.room_number be returned, when it is not the PK? (Completely gone out of my head). Should I make the unique key into a primary key?

bazz

[swampBoogie]

Presumably there are null values in the booked.room_number column

AND not exists (
          SELECT 1
             FROM booked b
           WHERE b.business_id = ?
               AND b.room_number = rp.room_number
               AND check_out_date >= ?   # arrival_requested
                 AND check_in_date <= ?    # departure_requested
                                     )

[guido2004]

Try this part of the query on its own, and see if it returns the values you expect:

SELECT b.room_number
                 FROM booked b
                 WHERE b.business_id = ?
                 AND b.room_number = rp.room_number
                 AND check_out_date >= ?   # arrival_requested
                 AND check_in_date <= ?    # departure_requested