Roy'sWiki

/*select schema*/
USE genealogy;
 
/*recursive cte1*/
WITH recursive anc1 AS(SELECT *,0000001 st FROM t_person WHERE id=1673247 UNION 
SELECT p.*,((2*anc1.st)+(p.Sexe='F')) st FROM t_person p JOIN anc1 ON anc1.idf=p.id OR anc1.idm=p.id),
 
/*recursive cte2*/
anc2 AS(SELECT *,0000001 st FROM t_person WHERE id=1673246 UNION 
SELECT p.*,((2*anc2.st)+(p.Sexe='F')) st FROM t_person p JOIN anc2 ON anc2.idf=p.id OR anc2.idm=p.id),
 
/*find intersection between cte1 and cte2*/
ancm AS(SELECT anc1.*,least(anc1.st,anc2.st) lst FROM anc1 JOIN anc2 USING(id)),
 
/*colapse double entries with smallest stradonits*/
ancf AS(SELECT id,idm,idf,Prenom,Nom,Sexe,MIN(lst) mst FROM ancm GROUP BY id,idm,idf,Prenom,Nom,Sexe)
 
/*acutual selection of result*/
SELECT *,strad(mst) strad FROM ancf;

WITH recursive anc1 AS(SELECT * FROM t_person WHERE id=1673247 UNION 
SELECT p.* FROM t_person p JOIN anc1 ON anc1.idf=p.id OR anc1.idm=p.id),
anc2 AS(SELECT * FROM t_person WHERE id=1673246 UNION 
SELECT p.* FROM t_person p JOIN anc2 ON anc2.idf=p.id OR anc2.idm=p.id) 
SELECT MAX(py) py,MAX(pr) pr,lat,lon,group_concat(u.id,': ',u.Nom,' | ',u.Prenom SEPARATOR '\n') p FROM 
(SELECT anc1.* FROM anc1 JOIN anc2 USING(id)) AS u 
JOIN plr USING(id) 
JOIN places ON n_lid=lid OR d_lid=lid 
WHERE lat IS NOT NULL GROUP BY lat,lon

WITH recursive anc AS(SELECT * FROM t_person WHERE id=9999999 UNION
SELECT p.* FROM t_person p JOIN anc ON p.id=anc.idm OR p.id=anc.idf)
SELECT * FROM anc WHERE idm IS NULL XOR idf IS NULL