SQL - Query multiple rooms with different Adults/Children per hotel

2
votes

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per hotel like this:

  • 1 Room with 2 Adults / 0 Children
  • 1 Room with 4 Adults / 2 Children
  • 1 Room with 2 Adults / 1 Children

Here is my query:

SELECT COUNT(pl.day) AS Days,
       p.property_ID AS Hotel_ID,
       p.name AS Hotel_Name,
       r.room_name AS Room_Name,
       r.room_type_ID AS Room_ID
FROM property p
INNER JOIN room_type r ON p.property_ID=r.property_ID
AND (r.max_adults >= 3
     AND r.max_children >= 0)
INNER JOIN plan pl ON pl.room_type_ID=r.room_type_ID
AND (pl.day >= "2014-07-07"
     AND pl.day <= "2014-07-11")
GROUP BY Room_ID,
         Hotel_ID HAVING Days = 4

EDIT

How do I add 'No_of_Room' in SELECT that differentiates the room_types by the room number, example result of a single room:

Array
(
    [Room_Price] => 160.00
    [Days] => 4
    [Hotel_ID] => 1
    [Hotel_Name] => Hotel Alfa
    [Room_Name] => Room type C
    [Room_ID] => 3
    [Max_Adults] => 3
    [Max_Children] => 1
    [No_of_Room] => 1 // What number of room does this room_type belongs to
)

Then I can show the results like:

enter image description here

EDIT

Rooms table

Rooms(
 ID,
 hotel_id
 room_name,
 max_Adults,
 max_Children
);

-- Populate
INSERT INTO Rooms VALUES (1,1,"Room A",2,1),(2,1,"Room B",2,5),(3,1,"Room C",3,0);
INSERT INTO Rooms VALUES (1,2,"Room A",2,1),(2,2,"Room B",2,5),(3,3,"Room C",3,4);
2
mysqlsqldatabasesearch
I've not read this in any great detail but I seriously recommend using views. Define a view using a select statement, it's a fake table (or just put that select statement in brackets and join on it). This is a great way to break the problem down. - Alec Teal
Never thought about it, do you have any example? - Stubbies
Post a create statement for your tables and some inserts for data, and I'll do it (leave a comment with @my name so I get notified) - Alec Teal

2 Answers

4
votes

EXAMPLES OF USING VIEWS TO MAKE THINGS NICER.

For this project authors may have aliases, for example one book may have "S. Lang" as the author, another might have "Serge Lang", the primary author is the main form (Serge Lang) and the secondaries are things like "S. Lang".

It is important to relate these, ideally I'd like a table with "AuthorId" and "PrimaryAuthorId" as columns, that way I could just select PrimaryAuthorId from it on AuthorId being equal to something.

To do this the view is defined as:

select
  `BookSystem_AuthorList`.`AuthorId` AS `AuthorId`,
  if((`BookSystem_AuthorList`.`duplicateOf` = 0),
    `BookSystem_AuthorList`.`AuthorId`,
    `BookSystem_AuthorList`.`duplicateOf`
  ) AS `PrimaryAuthorId`
from `BookSystem_AuthorList`;

Then

SELECT PrimaryAuthorId FROM BookSystem_PrimaryAuthorId WHERE AuthorId=10;

gives:

7

Which is much nicer for joining!

I then use this view to define another view (EditionAuthorsWithPrimaryId) - this gets the authors of an edition - and the primary author (I can then join to get names as needed)

select
  `BookSystem_EditionAuthors`.`BindingId` AS `BindingId`,
  `BookSystem_EditionAuthors`.`EditionId` AS `EditionId`,
  `BookSystem_EditionAuthors`.`AuthorId` AS `AuthorId`,
  `BookSystem_EditionAuthors`.`Position` AS `Position`,
  (select
    `BookSystem_PrimaryAuthorId`.`PrimaryAuthorId`
  from `BookSystem_PrimaryAuthorId`
  where (`BookSystem_PrimaryAuthorId`.`AuthorId` = `BookSystem_EditionAuthors`.`AuthorId`)
  ) AS `PrimaryAuthorId`
from `BookSystem_EditionAuthors`;

Now I can do:

SELECT * FROM BookSystem_EditionAuthorsWithPrimary WHERE EditionId=10;

BindingId, EditionId, AuthorId, Position, PrimaryAuthorId
10,         10,         10,         0,          7

Much nicer!

this next query is a great example

select
  `BookSystem_BookList`.`BookId` AS `Id`,
  `BookSystem_BookList`.`Title` AS `Name`,
  `BookSystem_BookList`.`UserId` AS `UserId`,
  `BookSystem_BookList`.`BookType` AS `Subtype`,
  1 AS `IsBook`,0 AS `IsSeries`,
  0 AS `IsAuthor`
from `BookSystem_BookList`

union

select
  `BookSystem_SeriesList`.`SeriesId` AS `Id`,
  `BookSystem_SeriesList`.`SeriesName` AS `Name`,
  `BookSystem_SeriesList`.`UserId` AS `UserId`,
  '' AS `Subtype`,
  0 AS `IsBook`,
  1 AS `IsSeries`,
  0 AS `IsAuthor`
from `BookSystem_SeriesList`

union

select
  `BookSystem_AuthorList`.`AuthorId` AS `Id`,
  concat(
    `BookSystem_AuthorList`.`AuthorSurname`,', ',`BookSystem_AuthorList`.`AuthorForename`,
    ifnull(
      (select concat(
        ' (AKA: ',
        group_concat(
          concat(
            `BookSystem_AuthorList`.`AuthorSurname`,
            ', ',
            `BookSystem_AuthorList`.`AuthorForename`
          ) separator '; '
        ),')'
      ) AS `AKA` from `BookSystem_AuthorList`
        where
          (`BookSystem_AuthorList`.`duplicateOf` = `Id`)
        group by (`BookSystem_AuthorList`.`duplicateOf` = `Id`)
    ),'')) AS `Name`,
    `BookSystem_AuthorList`.`UserId` AS `UserId`,
    '' AS `SubType`,
    0 AS `IsBook`,
    0 AS `IsSeries`,
    1 AS `IsAuthor`
from `BookSystem_AuthorList`
where (`BookSystem_AuthorList`.`duplicateOf` = 0) order by `Name`;

IS HUGE!

But now I can get all the things for UserId=1 easily:

mysql> SELECT * FROM BookSystem_Index WHERE UserId = 1;
+----+----------------------------------------+--------+-------------+--------+----------+----------+
| Id | Name                                   | UserId | Subtype     | IsBook | IsSeries | IsAuthor |
+----+----------------------------------------+--------+-------------+--------+----------+----------+
|  4 | A First Course in Calculus             |      1 | Normal      |      1 |        0 |        0 |
|  2 | A First Course in Real Analysis        |      1 | Normal      |      1 |        0 |        0 |
|  2 | Algebra                                |      1 |             |      0 |        1 |        0 |
| 13 | Analysis II assignments                |      1 | Assignments |      1 |        0 |        0 |
| 14 | Author Test                            |      1 | Normal      |      1 |        0 |        0 |
|  8 | b, g                                   |      1 |             |      0 |        0 |        1 |
|  7 | b, g (AKA: t, lll; Teal, lll)          |      1 |             |      0 |        0 |        1 |
|  1 | Calculus of Several Variables          |      1 | Normal      |      1 |        0 |        0 |
|  4 | DuBois, Paul                           |      1 |             |      0 |        0 |        1 |
|  1 | Lang, Serge (AKA: Lang, S. E. R. G. E) |      1 |             |      0 |        0 |        1 |
|  5 | Linear Algebra                         |      1 | Normal      |      1 |        0 |        0 |
|  3 | Morrey, C. B.                          |      1 |             |      0 |        0 |        1 |
|  6 | MySQL                                  |      1 | Normal      |      1 |        0 |        0 |
|  7 | Principles of Mathematical Analysis    |      1 | Normal      |      1 |        0 |        0 |
|  2 | Protter, M. H.                         |      1 |             |      0 |        0 |        1 |
|  5 | Rudin, Walter                          |      1 |             |      0 |        0 |        1 |
| 10 | t                                      |      1 | Normal      |      1 |        0 |        0 |
|  3 | Test                                   |      1 |             |      0 |        1 |        0 |
| 12 | Test 1                                 |      1 | Normal      |      1 |        0 |        0 |
| 11 | Test 4.4.2014                          |      1 | Normal      |      1 |        0 |        0 |
|  8 | Topology and Analysis                  |      1 | Normal      |      1 |        0 |        0 |
|  3 | Undergraduate Algebra                  |      1 | Normal      |      1 |        0 |        0 |
|  1 | Undergraduate Texts in Mathematics     |      1 |             |      0 |        1 |        0 |
|  9 | w                                      |      1 | Normal      |      1 |        0 |        0 |
+----+----------------------------------------+--------+-------------+--------+----------+----------+
24 rows in set (0.00 sec)

The optimiser sees the view properly, it wont generate the full view, it effectively substitutes the required selects.

(Taken from a testing DB, not production, hence weird names like "TESTING")

2
votes

First, the room type selection needs to be framed correctly. The following join would probably work.

EDIT:

The query has been edited to return only properties with all three room types. It has also been joined with the plan table.

SELECT 
    COUNT(pl.day) AS Days,
    p.property_ID AS Hotel_ID, 
    p.name AS Hotel_Name, 
    r.room_name AS Room_Name, 
    r.room_type_ID AS Room_ID,
    r.max_adults as Max_Adults,
    r.max_children as Max_Children
FROM property p
INNER JOIN room_type r
ON p.property_ID=r.property_ID
INNER JOIN plan pl 
ON pl.room_type_ID=r.room_type_ID
AND (pl.day >= '2014-07-07' AND pl.day <= '2014-07-11')
WHERE EXISTS
    (SELECT 1
    FROM room_type r1
    WHERE p.property_ID=r1.property_ID
    AND r1.max_adults = 2 AND r1.max_children = 0)
AND EXISTS
    (SELECT 1
    FROM room_type r2 
    WHERE p2.property_ID=r2.property_ID
    AND r2.max_adults = 4 AND r2.max_children = 2)
AND EXISTS
    (SELECT 1
    FROM room_type r3 
    WHERE P.PROPERTY_ID=R3.PROPERTY_ID
    AND r3.max_adults = 2 AND r3.max_children = 1)
GROUP BY 
    p.property_ID, 
    p.name, 
    r.room_name, 
    r.room_type_ID,
    r.max_adults,
    r.max_children
HAVING 
    COUNT(pl.day) = 4;