0

im trying to convert my inner join into a subquery but im not sure how, here is my code

select passenger.Email, passenger.First_Name, passenger.Last_Name, passenger.Mobile_Number,
       flight.Email, flight.Departure_Airport, flight.Departure_Date
from passenger
inner join flight on passenger.Email = flight.Email
where flight.Departure_Airport = 'jfk' 
and  flight.Departure_Date >= '2021-06-30';

can anyone explain how to do this or put me in the right direction?

Barmar
  • 741,623
  • 53
  • 500
  • 612
R3cklessRay
  • 5
  • 1
  • *im trying to convert my inner join into a subquery* What is the goal? – Akina Apr 27 '21 at 20:43
  • do you want to change the results somehow? there is no good way to use subqueries to do the same thing this query does – ysth Apr 27 '21 at 20:47
  • in terms of the goal i need to display departures with jfk and dates after 6/30/2021, im pretty sure the best way to do this is like how i did but i wanted to know how would you do it in a subquery way or if its even possible. – R3cklessRay Apr 27 '21 at 21:01
  • When using in sub-query, you usually just get values from one column back, so it could be `where passenger.Email IN (select Email from flight ...)` but that ruins your result set, since then you will not get the other values from the flight table. Unless you want to join to a sub-query. See [Selecting multiple columns/fields in MySQL subquery](https://stackoverflow.com/questions/5686271/selecting-multiple-columns-fields-in-mysql-subquery) for how to do that. I'd stick to the query you already have. – Scratte Apr 27 '21 at 21:03

1 Answers1

0

You can write a subquery that has the WHERE clause in it, and join with that.

select passenger.Email, passenger.First_Name, passenger.Last_Name, passenger.Mobile_Number,
       flight.Email, flight.Departure_Airport, flight.Departure_Date
from passenger
inner join (
    SELECT flight.Email, flight.Departure_Airport, flight.Departure_Date
    FROM flight
    where flight.Departure_Airport = 'jfk' 
    and  flight.Departure_Date >= '2021-06-30'
) AS flight on passenger.Email = flight.Email

MySQL should implement both queries the same.

Barmar
  • 741,623
  • 53
  • 500
  • 612