How to join To Select statements

Question

Note that DepartCountryID and ArrivalCountryID are two foreign keys of TravelCountryID

CREATE TABLE [dbo].[Airlines]
(
    [AirlineID] [bigint] IDENTITY(1,1) NOT NULL,
    [CompanyID] [int] NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [DepartCountryID] [bigint] NOT NULL,
    [ArrivalCountryID] [bigint] NOT NULL,
    [DepartTime] [datetime] NOT NULL,
    [ArrivalTime] [datetime] NOT NULL,

CREATE TABLE [dbo].[TravelCountries]
(
    [TravelCountryID] [bigint] NOT NULL,
    [CountryName] [nvarchar](50) NOT NULL,

This is the first select query:

SELECT 
    [Code], [DepartTime], [ArrivalTime], [CountryName] AS DepartCountry  
FROM 
    [Airlines], [TravelCountries] 
WHERE 
    [DepartCountryID] = [TravelCountryID])

Result:

1X2VC   2017-01-01 00:00:00.000 2017-01-01 03:30:00.000 Andorra
3VGH23  2018-01-10 18:45:00.000 2018-01-15 04:30:00.000 USA

The second select query:

SELECT 
    [Code], [DepartTime], [ArrivalTime], [CountryName] AS ArrivalCountry
FROM 
    [Airlines], [TravelCountries]
WHERE 
    [ArrivalCountryID] = [TravelCountryID])

Result:

1X2VC   2017-01-01 00:00:00.000 2017-01-01 03:30:00.000 France
3VGH23  2018-01-10 18:45:00.000 2018-01-15 04:30:00.000 England

I want the result be :

1X2VC   2017-01-01 00:00:00.000 2017-01-01 03:30:00.000 Andorra France
3VGH23  2018-01-10 18:45:00.000 2018-01-15 04:30:00.000 USA     England

Please, take a read to [JOINS](http://www.mysqltutorial.org/mysql-join/) and avoid using the `CROSS JOIN` using the comma separator operator. As a tip, the query you need will have to inner join two times the `TravelCountries` table, one time `ON TravelCountryID = DepartCountryID` and the other `ON TravelCountryID = ArrivalCountryID`. — Shidersz, Dec 26 '18 at 15:26
Changed the tags from MySQL into SQL Server, This SQL is not valid MySQL syntax but looks to be SQL Server syntax.. Also i advice you to read [this](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) and provide us with text formatted example data — Raymond Nijland, Dec 26 '18 at 15:28
[Bad habits to kick: Old style Joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Thom A, Dec 26 '18 at 15:52

score 2 · Accepted Answer · answered Dec 26 '18 at 15:56

As the data in the tables is not provided I cannot check the solution proposed but it should work. So, as stated Shidersz in the Comments, you should use JOINS (recommended LEFT JOIN to ensure that all Airlines are shown) and give sinonyms for the tables as you need to use the same table twice:

SELECT  [Airlines].[Code],
        [Airlines].[DepartTime],
        [Airlines].[ArrivalTime],
        TravelCountryDepart.[CountryName] as CountryDeparture,
        TravelCountryArrival.[CountryName] as CountryArrival 
FROM [Airlines]
LEFT JOIN [TravelCountries] TravelCountryDepart
    ON [Airlines].[DepartCountryID] = TravelCountryDepart.[TravelCountryID] 
LEFT JOIN [TravelCountries] TravelCountryArrival
    ON [Airlines].[ArrivalCountryID] = TravelCountryArrival.[TravelCountryID]

How to join To Select statements

1 Answers1