Check for multiple interchangable column values in SQL-CodePudding

I have 2 tables. The first one contains IDs of certain airports, the second contains flights from one airport to another.

ID   Airport 
---- ----
12  NYC
23  LOS
21  AMS
54  SFR
33  LSA

from  to   cost
---- ----  ----
12  23     500
12  23     250
23  12     200
12  12     50
12  12     90
21  23     100
54  12     400
33  21     700

I'd like to return a table where it contains ONLY airports that are interchangeable with a total cost.

Desired Output :

airport_1  airport_2   total_cost
----         ----        ----
 NYC         LOS         950
 NYC         NYC         140

CodePudding user response：

You can use the following query

select  max(a1.Airport)Airport_1,min(a2.Airport)Airport_2,sum(cost)total_cost
from

   (select *
   from flights
   where [from] in (select ID from airports where Airport = 'NYC' or Airport = 'LOS')
         and [to] in (select ID from airports where Airport = 'NYC' or Airport = 'LOS')) t1
      
      
   join airports a1 on t1.[from] = a1.ID
   join airports a2 on t1.[to] = a2.ID

group by 
    case 
            when 
               (a1.Airport = 'NYC' and a2.Airport = 'NYC')
            then 0
            
            when 
             (a1.Airport = 'NYC' and a2.Airport = 'LOS')
            then 1
          
            when 
               (a2.Airport = 'NYC' and a1.Airport = 'LOS')
            then 1
    end

Result

CodePudding user response：

 WITH AIRPORTS(ID,AIRPORT)AS
 (
   SELECT 12,  'NYC' UNION ALL
   SELECT 23,  'LOS' UNION ALL
   SELECT 21,  'AMS' UNION ALL
   SELECT 54,  'SFR' UNION ALL
   SELECT 33,  'LSA'  
),
DESTINATIONS(FROMM,TOO,COST)AS
(
  SELECT 12,  23,     500 UNION ALL
  SELECT 12,  23 ,    250 UNION ALL
  SELECT 23,  12,     200 UNION ALL
  SELECT 12,  12,     50 UNION ALL
  SELECT 12,  12,     90 UNION ALL
  SELECT 21,  23,     100 UNION ALL
  SELECT 54,  12,     400 UNION ALL
  SELECT 33,  21,     700
),
UQ AS
(
 SELECT GREATEST(D.FROMM,D.TOO)AS FLAG_1,LEAST(D.FROMM,D.TOO)AS FLAG_2, SUM(D.COST)AS TOTAL_COST,
COUNT(*)AS CNTT
FROM DESTINATIONS AS D
GROUP BY GREATEST(D.FROMM,D.TOO),LEAST(D.FROMM,D.TOO)
)
SELECT A.AIRPORT,A2.AIRPORT,Q.TOTAL_COST 
FROM UQ AS Q
JOIN AIRPORTS AS A ON Q.FLAG_1=A.ID
JOIN AIRPORTS AS A2 ON Q.FLAG_2=A2.ID   
WHERE Q.CNTT>1

Some small comments to the query: AIRPORTS and DESTINATIONS are the debug representation for your test data. The main job is done in the common table expression(CTE) UQ. The idea here is to present "interchangeable"-flights in the common form to perform required calculation. It is done by using GREATEST AND LEAST functions The last SELECT is to show the required output