Part 1: Rooms that are not reserved
It is a good practice to first streamline the task into simple words:
Select those rooms, which are not referred to in any reservation entry, and are from a given category.
What you in fact need here is not an
INNER JOIN. At least not for this part of the task.
INNER JOIN will be used to "link" with
INNER JOIN here could easily select rooms that are already reserved:
SELECT rooms.roomNR FROM rooms INNER JOIN reservation ON rooms.roomNR=reservation.roomNR WHERE catagory=$category_id. Of course you could process this information in PHP and use that to select the remaining rows in your table pretty efficiently, but there is a much better way.
You need a subquery.
SELECT roomNR FROM `rooms` WHERE catagory=$category_id AND roomNR NOT IN (SELECT roomNR FROM `reservation`)
It is a fortunate feature of MySQL (or SQL, in general), that the language syntax tries to resemble english written language as much as possible. Query explanation: we
roomNR of those
rooms that are not in the
SELECT roomNR FROM reservation result (or, simply put, the
For syntax and more information regarding subqueries, please check out http://dev.mysql.com/doc/refman/5.1/en/subqueries.html.
Part 2: PHP Implementation
To take this one step further, and follow your business logic, the following can be said:
Select those categories, that have rooms (at least one room) not referenced in reservation
And this is where you can implement an
INNER JOIN. Translating our problem to MySQL - assuming you have
name fields for
catagory - we have:
SELECT catagory.name FROM catagory INNER JOIN rooms ON catagory.id=rooms.catagory WHERE rooms.roomNR NOT IN (SELECT roomNR FROM reservation);
If I'm not mistaken, this query should query a given catagory name if there is at least one room with that catagory id not referenced in reservations. Please check back if this works for you, I'm not in an environment where I could test this right now.
If the query, by any means, returns a given
catagory multiple times, please use the
SELECT DISTINCT catagory.name FROM catagory ...
Part 3: Filter results by start and end date
To filter by start and end date, we come to the following problem:
Select those categories, that have at least one room not referenced in reservations. Reservations that are outside our time scope (example: reservation has already expired by the time the next user begins their own reservation) can be ignored, since their rooms are free, so they no longer limit our possibilities.
Variables used in the next query are these:
$start_date = $_POST['startDate']; $end_date = $_POST['endDate'];
To make it into SQL:
SELECT catagory.name FROM catagory INNER JOIN rooms ON catagory.id=rooms.catagory WHERE rooms.roomNR NOT IN (SELECT roomNR FROM reservation WHERE end<$start_date OR start>$end_date);
As you can see, we use an inverse logic in the subquery. When we are checking if a given room is already reserved (already in
reservation), we ignore those reservations that do not fall into time criteria: it has ended before next reservation begins, or starts before the next reservation ends. Therefore it won't be a limiting element for the
NOT IN clause.