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

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
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

Alternatively the following for people who have the surname prefixes installed, living persons are also excluded here.

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;