Inner join for a single table - mysql

Question

I have the following table

coaches( coach_code, coach_name, year_of_birth)

I need to create a query that returns pairs of coaches that were born at the same year and the year. every pair should appear only once. (coach 1, coach 2,year_of_birth)

the problem is that it's the same table and the inner join doesn't work.

any suggestions ?

Answer 1

SELECT c1.coach_code AS coach1, c2.coach_code AS coach2, c1.year_of_birth
FROM coaches AS c1
JOIN coaches AS c2 ON c1.year_of_birth = c2.year_of_birth AND c1.coach_code < c2.coach_code

Answer 2

You can use GROUP_CONCAT but keep in mind there is limit of characters in GROUP_CONCAT

SELECT GROUP_CONCAT(coach_name SEPARATOR ',') as `pair`,year_of_birth
FROM coaches
GROUP BY year_of_birth

GROUP_CONCAT(expr)