guy i was given a qeustion for my exam that
What is the most commonly booked room type for each hotel in London?
to this i replied
SELECT MAX(R.roomno)AS most room selected
FROM ROOM.R,HOTEL.H,BOOKING.B
GROUP BY roomtype
WHERE H.hotelno=B.hotelno AND city='london'my problem is that i dont know if the logic is correct or not and mysql server is not working i would be obliged if some one can tell me that my logic is true thank you all
the main reason why i wrote it like this was simple the most no of room repeatedly placed in booking table (where roomno is not a primary key )be an easy winner but i dont know if its possible
Your query will return the maximum room number. By maximum I mean the bigger number, not the most frequently booked.
For that you will need count() function grouped by room number. Also it requires to have the room number in the select (in order to see which one it is) and to order by count desc.
If you just want the room number and not the number of times it has been booked you need to create a query with the above as a sub-query (or at least this is the easiest way to do it).
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.