sql - mysql query room availability -

having looked @ of other mysql availability query questions on here has got me far, way mine works different others.

basically have table of dates, , each date row. each row contains fields cost, room type, , how many days booking has to book on date. if hotel has 1 room type, have 365 rows, if has 5 room types, have 1825 rows

bd_id   int(10) no  pri null    auto_increment bd_room_type    varchar(32) bd_date date    no bd_price    decimal(8,2) bd_h_id int(8)  no /* hotel id */ bd_available    tinyint(1) /* number of days must book include date */ 

i , dates, , fill in gap in between have dates booking.

$q1 = "select bd_h_id booking_dates bd_date in ('2011-02-16','2011-02-17','2011-02-18') , bd_available <= '3' , bd_room_type = 'single' , bd_price > '0' group bd_h_id having count(*) = '3'"; 

so if count same duration, means dependencies have been met , can show result in search.

i passing query variable query, extracts hotel info, checks see if sub query returning or not.

$q = "select c_title c_content c_id in ($q1) , if(($q1) > 0, 1, 0)"; 

this fine, if sub query returns more 1 hotel, main query gives me error:

subquery returns more 1 row

i thought because used 'in' ok. suggestions? when go implementing multiple sub queries, solution need work too.

i aware way doing rooms isnt elegant be, not sure how else achieve results need, because bookings bulk bookings (eg more 1 room booked @ time, if 1 room type isnt available during dates provided whole hotel needs removed search results).

if(($q1) > 0, 1, 0) 

this part returning error.

also, way have it, $q1 being evaluated twice, not want.

if understand correctly you're trying do, should able leave part out. if $q1 returns no rows, in expression won't match @ all.

should note in subqueries rather inefficient in mysql; run faster join:

select `c_title` `c_content` join ($q1) `a` on `c_content`.`c_id`=`a`.`bd_h_id`