SQL Join Duplicate Results

Question

I've been learning SQL to a slightly more advanced degree than usual, and I am a little bit stuck on this one.

The query is to get all of the airports that an airline flies to (destinations) from a user inputted origin.

SELECT DISTINCT a.airline_name, GROUP_CONCAT(ap.city) AS groupedDestinations
            FROM routes AS r
            LEFT JOIN airlines AS a
                ON r.airline = a.airline_iata
            LEFT JOIN airports AS ap
                ON r.destination = ap.airport_iata
            WHERE r.origin = ? AND a.active="Y" AND r.codeshare != "Y"
            GROUP BY a.airline_name ASC

The result should be:

Airline Name | city1, city2

Instead it is:

Airline Name | city1, city2, city1, city2

It's taken me a few hours to get to this point so I would appreciate any advice, abuse or answers :)

Thank you.

Answer 1

When using group byyou do not need distinct in the select clause.

The issue that you have is that airlines may have more than one route to a given city. This results in duplicates for the cities. By default, group_concat() does not remove these duplicates. To fix this, you want distinct in the group_concat:

SELECT a.airline_name, GROUP_CONCAT(distinct ap.city) AS groupedDestinations
            FROM routes AS r
            LEFT JOIN airlines AS a
                ON r.airline = a.airline_iata
            LEFT JOIN airports AS ap
                ON r.destination = ap.airport_iata
            WHERE r.origin = ? AND a.active="Y" AND r.codeshare != "Y"
            GROUP BY a.airline_name ASC;