Create a temp table from two tables, selecting the latest date before a specific date

Question

I have the following two tables

FlightTable

| passenger_id | flight_date       | flight_number  | destination  | 
| -------------| ----------        | -------------- | ------------ |
| 1234         | 2020-12-16        | 534            | CA           |
| 1234         | 2020-12-29        | 876            | FL           |
| 1234         | 2020-11-13        | 938            | FL           |
| 5678         | 2020-12-27        | 986            | MN           |
| 5678         | 2020-11-19        | 347            | WA           |

PassengerTable

| passenger_id | company_name | phone_number   | 
| -------------| ------------ | ------------   |
| 1234         | Verizon      | (555) 874-9232 |
| 5678         | AT&T         | (555) 867-5309 |

I want to create a temporary table that includes specific columns from both tables, but only includes the row from FlightTable that has the latest flight_date occurring before 2020-12-28 for each passenger. The resulting table should look like:

TempTable

| passenger_id | company_name |flight_number  | destination  | 
| -------------| ------------ |------------   | ------------ |         
| 1234         | Verizon      |534            | CA           |
| 5678         | AT&T         |986            | MN           |

I've tried the following query without success:

CREATE TEMPORARY TABLE TempTable AS (
SELECT F.passenger_id, P.company_name, flight_number, destination
FROM FlightTable AS F, PassengerTable AS P
WHERE '2020-12-28' <= MAX(flight_date) AND F.passenger_id = P.passenger_id  
);

How can this be modified to work as expected?

Answer 1

First, learn to use proper JOIN syntax. Then you can use a correlated subquery to select the most recent date before your cutoff:

SELECT F.passenger_id, P.company_name, f.flight_number, f.destination
FROM FlightTable F JOIN
     PassengerTable P
     ON F.passenger_id = P.passenger_id  
WHERE f.flight_date = (SELECT MAX(f2.flight_date) 
                       FROM FlightTable f2
                       WHERE f2.passenger_id = f.passenger_id AND
                             f2.flight_date < '2012-12-28'
                      );