1

I wanted to reduce the programming logic in a code that search hotels by using a single query.


Let say this is the database table. There are several contracts from a single hotel.

enter image description here

I wanted to get all the raws that satisfy input number of rooms for max_adults 1(capacity 1) rooms, number of rooms for rooms for max_adults 2(capacity 2) rooms and number of rooms for rooms for max_adults 3(capacity 3) rooms.

eg: 

Input : 
2 rooms of max_adults=1(capacity 1) 
1 rooms of max_adults=2(capacity 2) 
3 rooms of max_adults=3(capacity 3)
The rows that satisfy all the 3 conditions with the same hotel id must be output.

As in here the output must contain all the raws of hotel 1 and hotel 2

Since they are two separate results, an extra column is needed to show a generated result_id (kind of serial number as in here which must has duplicates acording to this problem).

I'm thinking lots of ways to do this, but nothing works well. Will this be possible to do in a single query?

Community
  • 1
  • 1
PrazSam
  • 1,166
  • 2
  • 12
  • 20

1 Answers1

1

Use a self join:

SELECT r1.HOTEL_ID, r1.MAX_ADULTS, r1.NO_OF_ROOMS,
    r2.MAX_ADULTS, r2.NO_OF_ROOMS,
    r3.MAX_ADULTS, r3.NO_OF_ROOMS,
FROM rooms AS r1
INNER JOIN rooms AS r2 ON r1.HOTEL_ID=r2.HOTEL_ID
INNER JOIN rooms AS r3 ON r1.HOTEL_ID=r3.HOTEL_ID
WHERE r1.MAX_ADULTS=1
AND r2.MAX_ADULTS=2
AND r3.MAX_ADULTS=3

You will have to add clauses to check the input number of rooms conditions.

The generated result_id would be r1.HOTEL_ID.

You would get a single row per hotel.

Oswald
  • 31,254
  • 3
  • 43
  • 68
  • Your answer going to save my day. Thanks. This is a kind of answer I expected. There may be several search results for a single hotel. So the hotel id can't be used as the generated id. How can it be done using the same query by enhancing it. – PrazSam Aug 06 '15 at 06:13
  • Oh I think since a single raw is a single result, If I can generate a normal serial id, It would be my result_id ! – PrazSam Aug 06 '15 at 06:36