Difference between revisions of "Report: Create a view of families marked living"
Jump to navigation
Jump to search
(Text replace - "<syntaxhighlight>" to "<syntaxhighlight lang="SQL">") |
(Text replace - "<syntaxhighlight lang="SQL">" to "<syntaxhighlight lang="SQL" enclose="div">") |
||
| Line 4: | Line 4: | ||
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 | 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 | ||
| − | --------<syntaxhighlight lang="SQL"> | + | --------<syntaxhighlight lang="SQL" enclose="div"> |
CREATE VIEW famliving AS SELECT h.lastname as fName, concat(h.title, ' ', h.firstname, ' ', h.suffix) | CREATE VIEW famliving AS SELECT h.lastname as fName, concat(h.title, ' ', h.firstname, ' ', h.suffix) | ||
AS hfName, f.marrdate, w.lastname AS wfName, concat(w.title, ' ', w.firstname, ' ', w.suffix) | AS hfName, f.marrdate, w.lastname AS wfName, concat(w.title, ' ', w.firstname, ' ', w.suffix) | ||
Latest revision as of 15:15, 18 June 2011
Description: Create a view of families marked living
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
CREATE VIEW famliving AS SELECT h.lastname as fName, concat(h.title, ' ', h.firstname, ' ', h.suffix)
AS hfName, f.marrdate, w.lastname AS wfName, concat(w.title, ' ', w.firstname, ' ', w.suffix)
AS wlName, f.familyID as FamilyID FROM tng_families f INNER JOIN tng_people h on f.gedcom = h.gedcom
and f.husband = h.personID INNER JOIN tng_people w on f.gedcom = w.gedcom and f.wife = w.personID
WHERE f.living = "1" AND h.living = "0" AND w.living = "0" ORDER BY f.marrdatetr
