I have two simple tables in SQLITE3 (family bonds):
“persons” (id, fname, lname)
“relationships” (parent/child)
I would like to get each grand children along with all their grand parents (from 1 to 4 of them depending on the grand child) so that 1 row result =
Distinct Grand child, Grand parent 1, Grand parent 2, Grand parent 3, Grand parent 4
Thanks to Caius Jard, I’ve been able to get each child and its grand parents in another star overflow question. However, so far I have:
1 line = Grand child, 1 grand parent (so it needs up to 4 lines to get all grand parents of a child).
SELECT c.fname, c.lname, gp.fname, gp.lname FROM relations p_c INNER JOIN relationships gp_p ON gp_p.child = p_c.parent INNER JOIN persons gp ON gp.id = gp_p.parent INNER JOIN persons c ON c.id = p_c.child ORDER BY c.id;
How could I edit this so that I get each grand child along with all the grand parents in one single row result?
If possible only using SELECT (+ count/distinct), WHERE (+in/exists), INNER/LEFT JOIN, GROUP BY (+having), ORDER BY.
Advertisement
Answer
Assuming there are four grandparents, you can enumerate them and aggregate:
SELECT fname, lname, MAX(CASE WHEN seqnum = 1 THEN grandparent_name END) as grandparent_1, MAX(CASE WHEN seqnum = 2 THEN grandparent_name END) as grandparent_2, MAX(CASE WHEN seqnum = 3 THEN grandparent_name END) as grandparent_3, MAX(CASE WHEN seqnum = 4 THEN grandparent_name END) as grandparent_4 FROM (SELECT c.id, c.fname, c.lname, gp.fname || ' ' || gp.lname as grandparent_name, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY gp.fname, gp.lanme) as seqnum FROM relations p_c JOIN relationships gp_p ON gp_p.child = p_c.parent JOIN persons gp ON gp.id = gp_p.parent persons c ON c.id = p_c.child ) cgp GROUP BY fname, lname, id;