SQLITE3 2 level join with aggreation on middle level

Question

we have 3 tables: grandfathers, fathers, sons. grandfathers have many fathers, and fathers have many sons, through the foreing keys grandfather_id and father_id. Each record has only one property, the name of the person.

What I want is to have a list of all grandfathers, each with a field called grandsons_names which is the csv of all grandsons names. Fathers' names don't appear anywhere.

Here's my attempt to solve the problem:

SELECT grandfathers.*, group_concat(sons.name) 
FROM grandfathers 
    LEFT JOIN fathers ON grandfathers.id == fathers.grandfather_id 
    LEFT JOIN sons ON fathers.id == sons.father_id;

problem is.. only one grandfather shows up because aggregation is happening in the wrong place.. how to fix this?

thanks

Answer 1

Have you tried it like that?

SELECT grandfathers.*, group_concat(sons.name) 
FROM grandfathers
LEFT JOIN fathers ON grandfathers.id = fathers.grandfather_id 
LEFT JOIN sons ON fathers.id = sons.father_id
GROUP BY grandfathers.id;