Basic SQL query

Question

Sorry for the basicness of this query, but I need a bit of help learning here :)

This is a database for a Hotel.

Here are my tables:

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

Where the bolded are the primary keys. I realise it isn't the best design to have a concatenated primary key for Bookings, but that is the way it is here.

I need a query for the following:

How many different guests have made a booking for August?

what are the queries you tried so far, if any? It does not look like you tried anything? — Olli
– Olli, Commented Aug 7, 2014 at 10:22
Don't mind the composite primary key.. do mind the inconsistent use of 'ID' and 'No' however, especially on hotel and room. — Arth
– Arth, Commented Aug 7, 2014 at 10:25
That was my mistake, Arth. They are all No. I used ID on the first few out of habit. @TheChaos, I am not sure how to use joins... so I am stumped from the beginning.. I can do it if I can get the two tables joined properly — Luke Beacon
– Luke Beacon, Commented Aug 7, 2014 at 10:26
Ah ok, as long as they are all consistent now, please update the question. — Arth
– Arth, Commented Aug 7, 2014 at 10:28
Do you just want it for total guests regardless of hotel/room, and also do you just want the count or do you want the details for the guests? — Rwd
– Rwd, Commented Aug 7, 2014 at 10:29

Arth · Accepted Answer · 2014-08-07 10:47:01Z

1

SELECT COUNT (DISTINCT guestno)
FROM   booking
WHERE  MONTH(datefrom) = 8

edited Aug 7, 2014 at 10:47

Arth

13.1k5 gold badges43 silver badges72 bronze badges

answered Aug 7, 2014 at 10:34

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arth Over a year ago

That will include any year, and won't include bookings that end in August but start before, or span August entirely.

Luke Beacon Over a year ago

This seems the easiest and simplest! Cheers mate

Luke Beacon Over a year ago

@Arth: Sorry, I should have specified more. I just need the bookings that have been made for August, as in dateFrom August.

Arth Over a year ago

@LukeBeacon No problem, I was working off "Just need a count of the unique guests at all hotels during August".. I'll leave my answer as is just in case you need that logic at some point.

strange_098 · Accepted Answer · 2014-08-07 10:26:19Z

1

Make a count of the number of guests during the month of July.

Then do the same for the month of August.

Make the difference between these two values and have the number of new guests that have made a booking for August.

Not a very good shape, however it is an option where you can start.

answered Aug 7, 2014 at 10:26

strange_098

1,4317 gold badges26 silver badges44 bronze badges

1 Comment

Arth Over a year ago

Whaaat? Why even think about the July bookings.. i think he means distinct guests.

Sathish · Accepted Answer · 2014-08-07 10:31:40Z

1

try like this

select guestName, guestAddress from Booking b 
inner join Guest g on b.guestNo=g.guestNo
inner join Hotel h on b.hotelNo=h.hotelID 
inner join Room r on b.roomNo=r.roomID
where b.hotelID=h.hotelID and 
MONTHNAME(dateFrom) = 'August' 
and MONTHNAME(dateTo)='August'

answered Aug 7, 2014 at 10:31

Sathish

4,4854 gold badges36 silver badges60 bronze badges

Comments

Arth · Accepted Answer · 2014-08-07 10:46:21Z

1

You don't actually need a JOIN:

SELECT COUNT(DISTINCT guestNo)
  FROM booking
 WHERE dateTo >= CONCAT(YEAR(CURDATE()),'-08');
   AND dateFrom < CONCAT(YEAR(CURDATE()),'-09');

This will include bookings that cover August entirely, or start or finish in August.. which I don't think the other answers cover.

I also assumed that you only wanted this for the current year.

edited Aug 7, 2014 at 10:46

answered Aug 7, 2014 at 10:35

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Comments

v2solutions.com · Accepted Answer · 2014-08-07 11:22:48Z

0

Below query should work for you,

SELECT 
       Booking.*,
       Guest.*
FROM   Guest
       JOIN Booking 
              ON Guest.guestNo = Booking
       LEFT JOIN Room 
              ON Booking.roomNo = Room.roomID
       LEFT JOIN Hotel 
              ON Room.hotelID = Hotel.hotelID
WHERE
       MONTH(datefrom) = '8'

There is one more suggestion on booking table composite key. Composite key for Booking table should be guestNo, roomNo, hotelNo, dateFrom

answered Aug 7, 2014 at 11:22

v2solutions.com

1,4399 silver badges8 bronze badges

Comments

Bjorg P · Accepted Answer · 2023-04-05 20:15:33Z

0

For Postgres 15

/6.15 How many different guests have made bookings for August?/

select 
    count(distinct guestno) as "Number of different guests have made bookings for August"
from 
    booking
where 
    (extract (month from (datefrom)) >= 8 and extract (month from (dateto)) <=8 ) or 
    (extract (month from (datefrom)) <= 8 and extract (month from (dateto)) >=8 ) or
    (extract (month from (datefrom)) = 8) or 
    (extract (month from (dateto)) = 8)
;

edited Apr 5, 2023 at 20:15

Bjorg P

1,0726 silver badges15 bronze badges

answered Mar 31, 2023 at 15:46

Violeta

1

Collectives™ on Stack Overflow

Basic SQL query

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related