CODEWITHSUNDEEP
mysqlsql
Luke Beacon
Luke Beacon

Reputation: 154

Basic SQL query

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:

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?

Upvotes: 2

Views: 1585

Answers (6)

Violeta
Violeta

Reputation: 1

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)
;

Upvotes: 0

v2solutions.com
v2solutions.com

Reputation: 1439

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 312259

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

Upvotes: 1

Arth
Arth

Reputation: 13110

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.

Upvotes: 1

Sathish
Sathish

Reputation: 4487

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'

Upvotes: 1

strange_098
strange_098

Reputation: 1391

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.

Upvotes: 1

Related Questions