CODEWITHSUNDEEP
mysqljoin
Meera
Meera

Reputation: 1

Query to join two tables

I am a newbie in MYSQL. Any help on this will be appreciated.

I have two tables Airports and Posts with the following Fields:

Airports 

id, Airport-code, Airport_name

Posts

id, Source_Airport_code, Destination_airport_code, Date_of_departure, preference

How do I get a record with the following fields (Here Source and Destination corresponds to Airport names instead of the codes):

Source, destination, date_of_departure

Upvotes: 0

Views: 520

Answers (2)

ain
ain

Reputation: 22749

If the FK in Posts.*_Airport_code doesn't refer to Airports.id as MJB assumes but to Airports.Airport_code then

SELECT
  APS.Airport_name AS Source,
  APD.Airport_name AS Destination,
  Posts.date_of_departure
FROM Posts
  INNER JOIN Airports APS ON(APS.Airport_code = Posts.Source_Airport_code)
  INNER JOIN Airports APD ON(APD.Airport_code = Posts.Destination_airport_code)

Upvotes: 2

MJB
MJB

Reputation: 7686

Try this to start:

select 
  s.airport_name as source, 
  d.airport_name as destination, 
  p.date_of_departure
from posts p
  inner join airports s
    on p.source_airport_code = s.id
  inner join airports d
    on d.source_airport_code = d.id

And I hate to say it, but I think you have a long way to go.

Upvotes: 0

Related Questions