selecting where falls on all dates....

[budgie]

Hi,

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.