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
23  12     200
21  23     100
54  12     400
33  21     700

I'd like to return a table where it contains ONLY airports that are interchangeable (NYC -LOS) in that case, with a total cost.

Desired Output :

airport_1  airport_2   total_cost
----         ----        ----
 NYC         LOS         700

CodePudding user response：

You can try this:

select c.Airport as airport_1,
d.Airport as airport_2,
(select (select cost from Table2 where "from" = a."from" and "to" = b."from")
  (select cost from Table2 where "from" = b."from" and "to" = a."from")) as cost
from Table2 a inner join Table2 b
on a."from" = b."to" and a."to" = b."from" and a."from" < b."from"
inner join Table1 c on a."from" = c.ID
inner join Table1 d on b."from" = d.ID

Fiddle

Basically you join the second table with itself to find the airports that are "interchangeable", then you join the result twice with the first table to get the names of the airports. Use a subqueries to get the total cost

CodePudding user response：

Basically self join the flight table with interchanged departure and destination and left join the airports to get their names.

SELECT a1.name AS airport_1,
       a2.name AS airport_2,
       f1.cost   f2.cost AS total_cost
       FROM flight AS f1
            INNER JOIN flight AS f2
                       ON f1."from" = f2."to"
                          AND f1."to" = f2."from"
                          AND f1."from" <= f2."from"
            LEFT JOIN airport AS a1
                      ON a1.id = f1."from"
            LEFT JOIN airport AS a2
                      ON a2.id = f1."to";

db<>fiddle

CodePudding user response：

You can get the result without need of a subquery by using LEAST() and GREATEST() functions along with HAVING clause such as

 SELECT MIN(airport) AS airport_1, MAX(airport) AS airport_2, SUM(cost)/2 AS total_cost
   FROM flights
   JOIN airports
     ON id IN ("from" , "to")
   GROUP BY LEAST("from","to"), GREATEST("from","to")
  HAVING COUNT(DISTINCT "from")*COUNT(DISTINCT "to")=4

where each pair(2) is counted twice(2) -> (2*2=4)

Demo

CodePudding user response：

I suspect for total clarity some more varied sample data might be needed, however see if the following works for you. First identify flights with matching from/to destinations, then partition by these and sum the costs for each partitioned window, returning the first of each match:

with m as ( /* Find flights with matching from/to */
    select *,
    case when exists (
      select * from flights f2 
        where f2."from"=f."to" and f2."to"=f."from"
    ) then least("from","to") else 0 end m
    from flights f
), c as ( /* partition by matches and sum cost */
      select *, Row_Number() over(partition by m order by "from") rn, 
      Sum(cost) over(partition by m) total_cost
    from m
    where m >0
)
select afrom.airport airport_1, ato.airport airport_2, total_cost
from c
join airports afrom on afrom.id=c."from"
join airports ato on ato.id=c."to"
where rn=1;

Example Fiddle