Report: Children with Spouse and Parents

Description: Lists all children, their spouse (if any) plus their Father and Mother. Replace 'yourtreename' with the name of your gedcom or treeID.

Based on code provided by Mark Wonson (on tngusers2 list).

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate

1. In this version 'living' individuals are included ONLY if the user has appropriate rights. The gedcom filter is only required if you wish the output to apply only to a specific tree.

SELECT
CONCAT('<a href="getperson.php?personID=',p.personid, '&tree=', p.gedcom,'">', p.personid,'</a>') as ChildID,
CONCAT(p.lastname,', ',p.firstname) AS Name,
p.birthdate,
p.birthplace,
p.deathdate,
p.deathplace,
CONCAT(h.firstname,' ',h.lastname) AS Father,
CONCAT(w.firstname,' ',w.lastname) AS Mother,
CASE WHEN p.sex = 'F' THEN
CONCAT (p2.firstname,' ',p2.lastname)
ELSE
CONCAT (p3.firstname,' ',p3.lastname)
END AS Spouse, p.living
FROM
tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID AND f.gedcom = h.gedcom
LEFT JOIN tng_people AS w ON f.wife = w.personID AND f.gedcom = w.gedcom
INNER JOIN tng_people AS p ON f.familyID = p.famc AND f.gedcom = p.gedcom
LEFT JOIN tng_families AS f2 ON  p.personID = f2.wife AND p.gedcom = f2.gedcom
LEFT JOIN tng_people AS p2 ON f2.husband = p2.personID AND f2.gedcom = p2.gedcom
LEFT JOIN tng_families AS f3 ON p.personID = f3.husband AND p.gedcom = f3.gedcom
LEFT JOIN tng_people AS p3 ON f3.wife = p3.personID AND f3.gedcom = p3.gedcom
WHERE f.gedcom = 'yourtreename'          
ORDER BY h.lastname, h.firstname, w.lastname, w.firstname

2. Alternatively the following version for people who have the surname prefixes installed, living persons are also excluded for ALL users.

SELECT 
CONCAT('<a href="getperson.php?personID=',p.personid, '&tree=', p.gedcom,'">', p.personid,'</a>') AS ChildID,
CONCAT(p.lnprefix,' ',p.lastname,', ', p.firstname) AS Name, p.birthdate, p.birthplace, p.deathdate, p.deathplace, CONCAT( h.firstname, ' ',h.lnprefix,' ', h.lastname ) AS Father, CONCAT( w.firstname, ' ', w.lastname ) AS Mother,
CASE WHEN p.sex = 'F'
THEN CONCAT( p2.firstname, ', ', p2.lnprefix,' ',p2.lastname )
ELSE CONCAT( p3.firstname, ', ', p3.lnprefix,' ',p3.lastname )
END AS Spouse
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
AND f.gedcom = h.gedcom
LEFT JOIN tng_people AS w ON f.wife = w.personID
AND f.gedcom = w.gedcom
INNER JOIN tng_people AS p ON f.familyID = p.famc
AND f.gedcom = p.gedcom
LEFT JOIN tng_families AS f2 ON p.personID = f2.wife
AND p.gedcom = f2.gedcom
LEFT JOIN tng_people AS p2 ON f2.husband = p2.personID
AND f2.gedcom = p2.gedcom
LEFT JOIN tng_families AS f3 ON p.personID = f3.husband
AND p.gedcom = f3.gedcom
LEFT JOIN tng_people AS p3 ON f3.wife = p3.personID
AND f3.gedcom = p3.gedcom
where p.living = '0'
ORDER BY p.lastname, h.lastname, h.firstname, w.lastname, w.firstname;