Determine total cost per user from 3 Different Tables

Question

I am working on a theatre booking system in MySql (My first SQL project). I have three tables:

Production (contains Title, BasicTicketPrice), 
Performance (PerformanceDate, PerformanceTime, Title) 
Booking (Email of person who booked, PerformanceDate, PerformanceTime, RowNumber). 

Each person booked tickets for two or three performances (using their email to book).

I need to to write a query which will display the prices paid for all booked seats and I need to output the RowNumber, Email of person who booked and the Calculated Price.

I understand that I need to join these tables and make the query display a temporary column called Calculated Price but I don't know how to calculate the price.

I tried this:

SELECT DISTINCT b.RowNumber, b.Email, pr.BasicTicketPrice 
FROM booking b, production pr performance p
WHERE p.Title=b.PerfDate*b.PerfTime*b.RowNumber;

SELECT CONCAT (PerfDate, PerfTime, RowNumber) AS BookingID FROM booking;

SELECT RowNumber, Email, CONCAT(PerfDate, PerfTime, RowNumber) AS BookingID FROM booking;

SELECT RowNumber, Email, CONCAT((CONCAT(PerfDate, PerfTime, RowNumber) AS BookingID 
   FROM booking)BasicTicketPrice*BookingID);

SELECT RowNumber, Email, CONCAT(PerfDate, PerfTime, RowNumber) AS BookingID INTEGER 
FROM booking;

SELECT RowNumber FROM booking
LEFT JOIN (SELECT Title FROM performance WHERE '2017-11-01 19:00:00' Email IS NULL);

But it didn't work.

Any suggestions? I will be grateful for any ideas.

Answer 1

Assuming:

• One row in Bookings per booked seat
• Title to be a suitable primary key for Production
• PerformanceDate, PerformanceTime to be a suitable primary composite key for Performance

You'll be looking to join the three tables together as per the keys assumed above. It seems you wish to group the bookings together per performance, by the person booking the tickets - if so, you'll need to use an aggregate to show the seat numbers (I've used GROUP_CONCAT to delimit them), as well as to COUNT the tickets purchased and multiply by the ticket cost.

SELECT 
   b.Email, prod.Title, per.PerformanceDate, per.PerformanceTime, 
   GROUP_CONCAT(RowNumber) AS BookedSeats, 
   COUNT(RowNumber) * prod.BasicTicketPrice AS TotalCost
FROM Booking b
INNER JOIN Performance per  
     ON b.PerformanceDate = per.PerformanceDate 
        AND b.PerformanceTime = per.PerformanceTime
INNER JOIN Production prod
     ON per.Title = prod.Title
GROUP BY 
   b.Email, prod.Title, per.PerformanceDate, per.PerformanceTime, prod.BasicTicketPrice
ORDER BY prod.Title, per.PerformanceDate, per.PerformanceTime;

Technically, we should include all non-aggregated columns in the GROUP BY, hence prod.BasicTicketPrice is listed as well.