What is the best way to perform a selection, a double condition in a JOIN?

Question

SQL

There are 2 tables:
1) Cities (name, country)
2) Directions (city of departure, city of arrival)
Somehow you need to get all the directions between the two countries ... I tried different combinations of queries, but they turned out to be very cumbersome and with their own problems.

Answer 1

SELECT DISTINCT CountryFrom.Name, CityFrom.Name, CountryTo.Name, CityTo.Name
  FROM Line
  INNER JOIN City AS CityFrom ON Line.CityFromID = CityFrom.ID 
  INNER JOIN Country AS CountryFrom ON CityFrom.CountryID = CountryFrom.ID 
  INNER JOIN City AS CityTo ON Line.CityToID = CityTo.ID 
  INNER JOIN Country AS CountryTo ON CityTo.CountryID = CountryTo.ID