Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Is this a correct query.

Posted on 2006-05-25
3
Medium Priority
?
292 Views
Last Modified: 2012-05-05
Hotel

HotelNo, HotelName, City

Room

RoomNo, HotelNo, Type, Price

Booking

HotelNo, GuestNo, DateFrom, DateTo, RoomNo

Guest

GuestNo, GuestName, GuestAddress

Room Type are P, D , S

What is the most commonly booked room type for each hotel in London?

SELECT MAX (R.TYPE)
FROM ROOM R, HOTEL H, Booked B
WHERE H.CITY= ‘London’
AND B.DATEFROM < SYSDATE AND
B.DATETO > SYSDATE
GROUP BY R.TYPE

Please let me know, if this is correct.

Thanks

0
Comment
Question by:Apu_Shah
3 Comments
 
LVL 2

Expert Comment

by:cwile
ID: 16761666
If your above query doesn't work, try something like this.

SELECT Room.HotelNo, Max(Room.Type) AS MaxType, Hotel.HotelNo, Hotel.HotelCity, Booking.HotelNo, Booking.DateFrom, Booking.DateTo
FROM (Room INNER JOIN Hotel ON Room.HoteNo = Hotel.HotelNo) INNER JOIN Booking ON Room.HotelNo = Booking.HotelNo
GROUP BY MaxType, Hotel.HotelCity, Booking.DateFrom, Booking.DateTo
HAVING (((Booking.DateFrom) < SYSDATE) And ((Booking.DateTo) > SYSDATE) AND ((Hotel.HotelCity)="London"))


(not sure if my syntax is completely correct, just kinda did it on a whim :p)
0
 
LVL 12

Accepted Solution

by:
wstuph earned 1500 total points
ID: 16762695
What's the database?

SQL Server method that I'd use:

select top 1 r.type, count(*) as RoomCount
FROM ROOM R
inner join HOTEL H
on r.hotelno = h.hotelno
inner join Booked B
on r.roomno = b.roomno
WHERE H.CITY= ‘London’
AND B.DATEFROM < SYSDATE AND
B.DATETO > SYSDATE
GROUP BY R.TYPE
order by count(*) desc
0
 
LVL 8

Expert Comment

by:mnrz
ID: 16764926
Hi
first of all I think the  conditions:  
    B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
are not necessary but, any way, as far sa I know, It's not possible to do this just by one select statement.
the following statement show you how many room booked in every hotel

Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type

I dont know which DataBase you are using but in Oracle it is possible to selecting from another select:

select name,type,max(cnt)
from (
Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type
)
Group By name
             ^

this "select" shows you how many times the most commonly room type booked
AND

select name,type,max(cnt)
from (
Select H.Hotel_Name as name,
          R.Type as type,
          Count(R.Type) as cnt
From Room R, Hotel H,Booked B
Where
     H.Hotel_No = B.Hotel_No and
     H.Hotel_No = R.Hotel_No and
     R.Room_No = B.Room_No and
     H.City = "London" and
     B.DateFrom < SYSDATE and
     B.DateTo > SYSDATE
Group By name,type
)
Group By type
             ^

this one shows you which types are most commonly booked

eventually you can mix these two result and obtain your goal.

I tested by some sample data:

THIS IS THE FIRST SELECT STATEMENT:
mysql>  select  type,name,count(name) from h group by type,name;
+------+-------+---------------+
| type | name| count(name) |
+------+-------+---------------+
| d      | h1   |                  2 |
| d      | h2   |                  7 |
| d      | h3   |                  1 |
| m     | h1   |                  1 |
| m     | h2   |                  2 |
| s      | h1   |                  4 |
| s      | h2   |                  2 |
| s      | h3   |                  2 |
+------+------+----------------+
8 rows in set (0.00 sec)

THESE ARE TWO NEXT SELECT STATEMENT:

mysql> select name,type,max(cnt) from s group by type;
+--------+-----+--------------+
| name  | type | max(cnt)   |
+--------+------+-------------+
| h1      | d     |        7        |
| h1      | m    |        2        |
| h1      | s     |        4        |
+--------+-----+--------------+
3 rows in set (0.00 sec)

mysql> select name,type,max(cnt) from s group by name;
+--------+------+-----------+
| name  | type | max(cnt) |
+--------+------+-----------+
| h1      | d     |            4 |
| h2      | d     |            7 |
| h3      | d     |            2 |
+--------+------+-----------+
3 rows in set (0.00 sec)

datas, types and the names are arbitrary, and are just for test. but you can look at both result and compound them to find the final goal.

but I think you better create a stored procedure or use programming to solve the problem.

hope this help

regards
mohammad
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question