There is a table in MS SQL SERVER 2008

SELECT TOP 1000 [NumberOfFlight] ,[AviacompanyID] ,[PlaneID] ,[toID] ,[fromID] ,[GoDateTime] ,[ArriveDateTime] ,[CostOfEcoClass] ,[CostOfBusinessClass] ,[EcoSold] ,[BusSold] ,[AllSold] ,[Money] FROM [airport].[dbo].[FlightsCurrentSet]

The toID and fromID take values ​​from the same table. I wrote this request:

 SELECT AviacompaniesSet.Name AS Авиакомпания, PlanesSet.Model AS Самолёт, CitiesSet.Name AS Откуда, CitiesSet.Name AS Куда, GoDateTime AS [Дата и время отправления], ArriveDateTime AS [Дата и время прибытия], CostOfEcoClass AS [Стоимость эконом класса], CostOfBusinessClass AS [Стоимость бизнес класса], EcoSold AS [Продано билетов эконом класса], BusSold AS [Продано билетов бизнес класса], AllSold AS [Продано всего], [Money] AS [Прибыль] FROM ([airport].[dbo].FlightsCurrentSet INNER JOIN [airport].[dbo].AviacompaniesSet ON AviacompaniesSet.Id = FlightsCurrentSet.AviacompanyID) INNER JOIN [airport].[dbo].PlanesSet ON PlanesSet.Id = FlightsCurrentSet.PlaneID INNER JOIN [airport].[dbo].CitiesSet ON CitiesSet.Id = FlightsCurrentSet.toID AND CitiesSet.Id = FlightsCurrentSet.fromID

But nothing comes from me. He brings me the same city. Moreover, it does not display all the rows contained in this table.

  • Give an example of your results and an example of expected results. - null
  • Attach the tables to the query one by one and see the resulting data to understand at what stage something is going wrong - Mike
  • Comrade @ i-one answered my question. Thank you all for your help. - Andrew
  • Just read, and there will be no such questions. These are the basics - SanŚ́́́́́́́́́́-

2 answers 2

You probably want to join the same table twice. For this they need to be given different alias. In general, in my opinion, with alias, the code is more compact and readable.

Something like this:

 SELECT avc.Name AS Авиакомпания, ps.Model AS Самолёт, ctyFrom.Name AS Откуда, ctyTo.Name AS Куда, ... FROM [airport].[dbo].FlightsCurrentSet fc INNER JOIN [airport].[dbo].AviacompaniesSet avc ON avc.Id = fc.AviacompanyID INNER JOIN [airport].[dbo].PlanesSet ps ON ps.Id = fc.PlaneID INNER JOIN [airport].[dbo].CitiesSet ctyFrom ON ctyFrom.Id = fc.fromID INNER JOIN [airport].[dbo].CitiesSet ctyTo ON ctyTo.Id = fc.toID
  • Yes! This is exactly what I need. It works exactly like I wanted. Thank you very much. - Andrew

Make two INNER JOIN:

 SELECT AviacompaniesSet.Name AS [Авиакомпания], PlanesSet.Model AS [Самолёт], CSFrom.Name AS [Откуда], CSFrom.Name AS [Куда], GoDateTime AS [Дата и время отправления], ArriveDateTime AS [Дата и время прибытия], CostOfEcoClass AS [Стоимость эконом класса], CostOfBusinessClass AS [Стоимость бизнес класса], EcoSold AS [Продано билетов эконом класса], BusSold AS [Продано билетов бизнес класса], AllSold AS [Продано всего], [Money] AS [Прибыль] FROM ([airport].[dbo].FlightsCurrentSet INNER JOIN [airport].[dbo].AviacompaniesSet ON AviacompaniesSet.Id = FlightsCurrentSet.AviacompanyID) INNER JOIN [airport].[dbo].PlanesSet ON PlanesSet.Id = FlightsCurrentSet.PlaneID INNER JOIN [airport].[dbo].CitiesSet CSFrom ON CSFrom.Id = FlightsCurrentSet.fromID INNER JOIN [airport].[dbo].CitiesSet CSTo ON CSTo.Id = FlightsCurrentSet.toID

I also note that airplanes do not fly between cities, but between airports. And besides, the idea should be about flights, and already on flights a specific aircraft is assigned.

  • Thanks for the amendment. I will consider - Andrew