mysql query room availability

Having looked at some of the other mysql availability query questions on here has got me so far, but the way mine works is slightly different to the others.

Basically I have a table of dates, and each date is a row. Each row contains fields to say the cost, room type, and how many days your booking has to be to book on that date. So if a Hotel has 1 room type, it will have 365 rows, if it has 5 room types, it will 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 you must book to include this date */

I get the from and to dates, and fill in the gap in between so I have all the dates for the booking.

$q1 = "SELECT bd_h_id
FROM booking_dates
WHERE bd_date IN ('2011-02-16','2011-02-17','2011-02-18')
AND bd_available <= '3'
AND bd_room_type = 'single'
AND bd_price > '0'
GROUP BY bd_h_id
HAVING count(*) = '3'";

So if the count is the same as the duration, it means all dependencies have been met and it can show the result in the search.

I am then passing that query as a variable into another query, that extracts the hotel info, checks to see if the sub query is returning anything or not.

$q = "SELECT c_title FROM c_content WHERE c_id IN ($q1) AND IF(($q1) >开发者_运维技巧 0, 1, 0)";

This is fine, however if the sub query returns more than 1 hotel, the main query gives me the error:

Subquery returns more than 1 row

I thought because I used 'IN' that it would be ok. Any suggestions? Also when I go about implementing multiple sub queries, the solution will need to work with that too.

I am aware that the way I am doing the rooms isnt as elegant as it could be, however I am not sure how else to achieve the results I need, as because the bookings will usually be bulk bookings (eg more than one room booked at a time, if one room type isnt available during the dates provided then the whole hotel needs to be removed from the search results).

---

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

This is the part returning the error.

Also, the way you have it, $q1 is being evaluated twice, which is probably not what you want.

If I understand correctly what you're trying to do, then you should be able to just leave this part out. If $q1 returns no rows, then the IN expression simply won't match anything at all.

Should note as well that IN with subqueries is rather inefficient in MySQL; would run much faster with a join:

SELECT `c_title` FROM `c_content` JOIN ($q1) `a` ON `c_content`.`c_id`=`a`.`bd_h_id`

---

The error message is referring to your IF(($q1) > 0, 1, 0) clause at the end of the query. You can't compare a subquery with > if it returns more than one row.