Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: selecting where data falls on ALL dates

    Hi there!,

    I have a table with availability of rooms which has the
    htlrm_id: hotel room id
    avl_date: date this room is available
    avl_qty: quantity of this room available
    avl_rate: rate at which these rooms can be booked

    an example of the data in the table is:

    htlrm_id avl_date avl_qty avl_rate
    2 20031005 14 140
    2 20031006 3 20
    2 20031007 4 40
    5 20031005 1 100
    5 20031006 1 100
    5 20031007 0 120

    My customer wishes to see whats available from 20031005 to 20031007 inclusive -- they just want to book one room.

    Hotel room 5 is only available for 2 of those days so I only want to return the rows for htl_id 2.

    Using the statement:
    SELECT
    `tbl_availability_temp`.`htlrm_id`,
    `tbl_availability_temp`.`avl_date`,
    `tbl_availability_temp`.`avl_qty`,
    `tbl_availability_temp`.`avl_rate`
    FROM
    `tbl_availability_temp`
    WHERE
    ((`tbl_availability_temp`.`avl_date` BETWEEN '2003-10-05' AND '2003-10-07') AND
    (`tbl_availability_temp`.`avl_qty` > 0))

    is bringing back:
    htlrm_id avl_date avl_qty avl_rate
    2 20031005 14 140
    2 20031006 3 20
    2 20031007 4 40
    5 20031005 1 100
    5 20031006 1 100

    where I want to return:
    htlrm_id avl_date avl_qty avl_rate
    2 20031005 14 140
    2 20031006 3 20
    2 20031007 4 40

    I'm working off mysql 3.23.53 --- so can't use nested selects like I normally would.....

    Any suggestions? I'd ideally like to keep it all on one statement...if possible that is.

    I've included the sql to create the tables and data:

    CREATE TABLE `tbl_availability_temp` (`htlrm_id` smallint(3) unsigned NOT NULL default '0',
    `avl_date` timestamp(8) NOT NULL,
    `avl_qty` tinyint(3) unsigned NOT NULL default '0',
    `avl_rate` decimal(4,2) NOT NULL default '0.00',
    PRIMARY KEY (`htlrm_id`,`avl_date`),
    KEY `htlrm_id` (`htlrm_id`,`avl_date`)
    ) TYPE=MyISAM;

    INSERT INTO `tbl_availability_temp` (`htlrm_id`, `avl_date`, `avl_qty`, `avl_rate`) VALUES
    (2,'2003-10-05',14,140),
    (2,'2003-10-06',3,20),
    (2,'2003-10-07',4,40),
    (5,'2003-10-05',1,100),
    (5,'2003-10-06',1,100),
    (5,'2003-10-07',0,120);

    I'm working with php 4.3.2 and mysql 3.23.53.

    Anyone have and ideas ...?

    ta

  2. #2
    Join Date
    Jul 2003
    Posts
    30

    Re: selecting where data falls on ALL dates

    select t1.htlrm_id from tbl_availability_temp as t1 where t1.avl_date between "20031005" and "20031007" and t1.avl_qty > 0 group by t1.htlrm_id having count(*) = 3;


    this isn't exactly what you want, but it gets you the htlrm_id of the room that fits your criteria ...

    Just change the count(*) = 3 to however many nights that is

Posting Permissions

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