CODEWITHSUNDEEP
sqlpostgresql
fuxxy
fuxxy

Reputation: 43

Im making a bus booking system in Postgresql and got stuck on how to check if seats is available while booking

How can I make some kind of statement checking if the seats isn't more than the number of seats that a bus actually have?

Right now I have the following tables: Booking (route_id, mail, seats) --route_id foreign key to route(route_id) seats = number of seats to book

Bus (bus_id, seats, driver_ssc)

City (country, city_name, bus_stop, route_id)

Driver (name, driver_ssc, phone, adress)

Route (dep, arrival, dep_time, arrival_time, price, bus_id, route_id)

Traveler (fn, ln, phone, adress, mail, user_id)

Some information is just to fulfill the assignment I've got.

Please give suggestions on how to control the seats while booking. Ask if I need to explain anything that's not clear. Thank you!

Upvotes: 0

Views: 205

Answers (1)

Piyapan Poomsirivilai
Piyapan Poomsirivilai

Reputation: 329

I understand that 1 unique route_id has only 1 unique bus_id, right? If correct, This query may help you.

with booking_summary as (
    select 
        route_id, 
        sum(seats) as "total_book_seats"
    from
        booking
    group by route_id
)

select 
    route.route_id, 
    bus.bus_id, 
    booking_summary.total_book_seats, 
    bus.seats 
from
    booking_summary
        join route on booking_summary.route_id = route.route_id
        join bus on route.bus_id = bus.bus_id
where 
    booking_summary.total_book_seats > bus.seats

The concept is to calculate total booked seats into booking_sumary temp table. Then, join the temp table to other tables in order to get a number of bus seats.

Upvotes: 1

Related Questions