CODEWITHSUNDEEP
mysqlsqlcalculated-columns
Stumbler
Stumbler

Reputation: 2146

How to store SQL Query result in table column

I'm aware of the INSERT INTO table_name QUERY; however, I'm unsure how to go about achieving the desired result in this case.

Here's a slightly contrived example to explain what I'm looking for, but I'm afraid I cannot put it more succiently.

I have two tables in a database designed for a hotel.

BOOKING and CUSTOMER_BOOKING

Where BOOKING contains PK_room_number, room_type, etc. and CUSTOMER_BOOKING contains FK_room_number, FK_cusomer_id

CUSTOMER_BOOKING is a linking table (many customers can make many bookings, and many bookings can consist of many customers).

relationship

Ultimately, in the application back-end I want to be able to list all rooms that have less than 3 customers associated with them. I could execute this a separate query and save the result in the server-side scripting.

However, a more elegant solution (from my point of view) is to store this within the BOOKING table itself. That is to add a column no_of_bookings that counts the number of times the current PK_room_number appears as the foreign key FK_room_number within the CUSTOMER_BOOKING table. And why do this instead? Because it would be impossible for me to write a single complicated query which will both include the information from all ROOMS, among other tables, and also count the occurrences of bookings, without excluding ROOMS that don't have any bookings. A very bad thing for a hotel website attempting to show free rooms!

So it would look like this

BOOKING: PK_room_number (104B) room_type (double) room_price (high), no_of_bookings (3) BOOKING: PK_room_number (108C) room_type (single) room_price (low), no_of_bookings (1) CUSTOMER_BOOKING: FK_room_number (104B) FK_customer_id (4312) CUSTOMER_BOOKING: FK_room_number (104B) FK_customer_id (6372) CUSTOMER_BOOKING: FK_room_number (104B) FK_customer_id (1112) CUSTOMER_BOOKING: FK_room_number (108C) FK_customer_id (9181)

How would I go about creating this?

Upvotes: 1

Views: 2613

Answers (4)

Parfait
Parfait

Reputation: 107587

Consider below solutions.

A simple aggregate query to count the customers per each booking:

SELECT b.PK_room_number, Count(c.FK_customer_id) 
FROM Booking b
INNER JOIN Customer_Booking c ON b.PK_room_number = c.FK_room_number
GROUP BY b.PK_room_number
HAVING Count(c.FK_customer_id) < 3;  # ADD 3 ROOM MAX FILTER

And if you intend to use a new column no_of_booking, here is an update query (using aggregate subquery) to run right after inserting new value from web frontend:

UPDATE Booking b 
INNER JOIN 
  (SELECT b.PK_room_number, Count(c.FK_customer_id) As customercount
   FROM Booking b
   INNER JOIN Customer_Booking c ON b.PK_room_number = c.FK_room_number
   GROUP BY b.PK_room_number) As r
ON b.PK_room_number = r.PK_room_number
SET b.no_of_booking = r.customercount;

Upvotes: 1

MarioAna
MarioAna

Reputation: 865

You could do it in a single straight select like this:

select DISTINCT 
       b1.room_pk,
       c1.no_of_bookings
  from cust_bookings b1,
       (select room_pk, count(1) as no_of_bookings
          from cust_bookings
         group by room_pk) c1
 where b1.room_pk = c1.room_pk 
 having c1.no_of_bookings < 3

Sorry i used my own table names to test it but you should figure it out easily enough. Also, the "having" line is only there to limit the rows returned to rooms with less than 3 bookings. If you remove that line you will get everything and could use the same sql to update a column on the bookings table if you still want to go that route.

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

Because it would be impossible for me to write a single complicated query which will both include the information from all ROOMS, among other tables, and also count the occurrences of bookings, without excluding ROOMS that don't have any bookings.

I wouldn't say it's impossible and unless you're running into performance issues, it's easier to implement than adding a new summary column:

select b.*, count(cb.room_number) 
from bookings b
left join customer_booking cb on b.room_number = cb.room_number
group by b.room_number

Depending on your query may need to use a derived table containing the booking counts for each room instead instead

select b.*, coalesce(t1.number_of_bookings,0) number_of_bookings
from bookings b
left join (
    select room_number, count(*) number_of_bookings
    from customer_booking
    group by room_number
) t1 on t1.room_number = b.room_number

You have to left join the derived table and select coalesce(t1.number_of_bookings,0) in case a room does not have any entries in the derived table (i.e. 0 bookings).

A summary column is a good idea when you're running into performance issues with counting the # of bookings each time. In that case I recommend creating insert and delete triggers on the customer_booking table that either increment or decrement the number_of_bookings column.

Upvotes: 1

William Jones
William Jones

Reputation: 204

the following generates a list showing all of the bookings and a flag of 0 or 1 if the the room has a customer for each of the rooms. it will display some rooms multiple times if there are multiple customers.

select BOOKING.*, case CUSTOMER_BOOKING.FK_ROOM_NUMBER is null THEN 0 ELSE 1 END AS BOOKING_FLAG from BOOKING LEFT OUTER JOIN CUSTOMER_BOOKING ON BOOKING.PK_room_numer = CUSTOMER_BOOKING.FK_room_number

summing and grouping we arrive at:

select BOOKING.*, SUM(case when CUSTOMER_BOOKING.FK_ROOM_NUMBER is null THEN 0 ELSE 1 END) AS BOOKING_COUNT from BOOKING LEFT OUTER JOIN CUSTOMER_BOOKING ON BOOKING.PK_room_number = CUSTOMER_BOOKING.FK_room_number GROUP BY BOOKING.PK_room_number

there are at least two other solutions I can think of off the top of my head...

Upvotes: 0

Related Questions