Nuno Pinto Nuno Pinto - 6 months ago 10
SQL Question

Select TOP columns from table1, join table2 with their names

I have a TABLE1 with these two columns, storing departure and arrival identifiers from flights:

dep_id arr_id

1 2
6 2
6 2
6 2
6 2
3 2
3 2
3 2
3 4
3 4
3 6
3 6


and a TABLE2 with the respective IDs containing their ICAO codes:

id icao
1 LPPT
2 LPFR
3 LPMA
4 LPPR
5 LLGB
6 LEPA
7 LEMD


How can i select the top count of TABLE1 (most used departure id and most used arrival id) and group it with the respective ICAO code from TABLE2, so i can get from the provided example data:

most_arrivals most_departures
LPFR LPMA


It's simple to get ONE of them, but mixing two or more columns doesn't seem to work for me no matter what i try.

Answer

try this one:

select
    (select name
        from table2 where id = (
        select top 1 arr_id
        from table1
        group by arr_id
        order by count(*) desc)
    )  as most_arrivals,
    (select name
        from table2 where id = (
        select top 1 dep_id
        from table1
        group by dep_id
        order by count(*) desc)
    ) as most_departures
Comments