SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not in clause broken

    Hi,

    Almost at my wits end here

    This is my table

    Code mysql:
    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

    Code mysql:
    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
    Last edited by IBazz; Mar 11, 2009 at 19:05.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Presumably there are null values in the booked.room_number column

    Code:
    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
                                         )

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try this part of the query on its own, and see if it returns the values you expect:
    Code:
    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


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
  •