CODEWITHSUNDEEP
javamysqlsql
Cethy
Cethy

Reputation: 551

Calculate value of attribute using number of occurences in another table?

I'm trying to create a really simple "travel reservation"-application to learn more about databases. I've gotten everything to work except for one thing:

One of my tables contains the trips available, and one of the columns is "number_of_seats" which simply states the maximum number of seats for each trip.

One of my other tables is responsible for holding the reservations made by the different users, where each row represents an individual reservation.

The problem:

I want to get the number of seats available for a specific trip by counting the references to the trip_id in the "reservation"-table, subtract that from the value in "number_of_seats", and get the result for each row.

What would that kind of query look like? Is it even possible?

Upvotes: 0

Views: 39

Answers (1)

Brendan
Brendan

Reputation: 1247

Try something like this...

SELECT number_of_seats -  
  (SELECT COUNT(*) FROM reservations WHERE reservations.trip_id = trips.trip_id)
FROM trips

Upvotes: 1

Related Questions