Sql Join , updating one table and generating output from another table

Question

CREATE TABLE LeadCustomer 
( 
  CustomerID INTEGER NOT NULL, FirstName VARCHAR(20) NOT NULL, 
  Surname VARCHAR(40) NOT NULL, BillingAddress VARCHAR(200) NOT NULL, 
  email VARCHAR(30) NOT NULL    
)

CREATE TABLE Flight 
(
 FlightID INTEGER NOT NULL, FlightDate TIMESTAMP NOT NULL, 
 Origin VARCHAR(30) NOT NULL, Destination VARCHAR(30) NOT NULL, 
 MaxCapacity INTEGER NOT NULL, PricePerSeat DECIMAL NOT NULL
)

CREATE TABLE FlightBooking 
(
 BookingID INTEGER NOT NULL, CustomerID INTEGER NOT NULL, FlightID INTEGER NOT NULL,
 NumSeats INTEGER NOT NULL, Status CHAR(1) NOT NULL, BookingTime TIMESTAMP NOT NULL, 
 TotalCost DECIMAL 
)

If the flight is rescheduled, how can we generate a list of customers who needs to be contacted ?

So far I have completed up to:

SELECT
LeadCustomer.CustomerID,Flight.FlightID 
FROM LeadCustomer 
inner join FlightBooking 
ON LeadCustomer.CustomerID = FlightBooking.CustomerID            
inner join 
Flight ON FlightBooking.FlightID = Flight.FlightID;

but don't know how to update the flight time

Norberto108 · Answer 1 · 2013-04-10T15:35:19.387

I understand that Flight.FlightDate is equal to FlightBooking.BookingTime. When you update the first column you can search where one became different from the other, like:

SELECT
LeadCustomer.CustomerID,Flight.FlightID 
FROM LeadCustomer 
inner join FlightBooking 
ON LeadCustomer.CustomerID = FlightBooking.CustomerID            
inner join 
Flight ON FlightBooking.FlightID = Flight.FlightID
WHERE Flight.FlightDate <> FlightBooking.BookingTime

If I´m wrong could you explain it better

Sql Join , updating one table and generating output from another table

1 Answers1