Veel gestelde vragen This is a new feature at this site. An interactive way to talk about the genealogies

The owner of this website pays about 400 dollar per month to keep this webiste in the air. In order to view the data follow this link donate any amount you want. Now also possible on a bankaccount in the Netherlands, made possible by the familybank . The site gets 80.000 hits daily. Please click on the advertisements to generate money for me

Home Search Login Your Bookmarks  
Share Print Bookmark


Report: Report List and code, lijst met alle rapporten en code

         Description: If the tree is mentioned as savenije please replace with your won tree. Als de naam van de tree=savenije vervang die door uw eigen stamboom naam


Matches 1 to 248 of 248   » Comma-delimited CSV file

# reportID Report Name reportdesc sqlselect active
1 100  families: occuring marriage types without names (but with frequency)  families: occuring marriage types without names (but with frequency) one = equals 5 people
Gezinnen: "typen huwelijk" zonder namen maar met aantallen, een = is 5 mensen 
SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD('',COUNT(*)/5,'=') AS Graph FROM tng_families WHERE marrtype<>'' GROUP BY marrtype ORDER BY marrtype; 
2 107  individuals with missing father or missing mother  individuals with missing father or missing mother   SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS fatherNr, father.lastname AS Name1, father.firstname AS firstname1, father.living, mother.personID AS motherNr, mother.lastname AS Name2, mother.firstname AS firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS p ON c.personID=p.personID LEFT JOIN tng_people AS mother ON f.wife=mother.personID LEFT JOIN tng_people AS father ON f.husband=father.personID WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdatetr;  
3 265  Age in weeks of Children who died before 1  Leeftijd in weken van kinderen die stierven voor ze 1 werden  SELECT personID, lastname, firstname, birthdate AS Birth, deathdate AS Death, ROUND( DATEDIFF( deathdatetr, birthdatetr ) /7 ) AS weeks
FROM tng_people
WHERE DATEDIFF( deathdatetr, birthdatetr ) >1
AND DATEDIFF( deathdatetr, birthdatetr ) <365
AND living =0
AND YEAR( birthdatetr ) !=0
AND YEAR( deathdatetr ) !=0
ORDER BY weeks DESC  
4 266  Age in years, weeks, days  Leeftijd in jaren, weken en dagen  SELECT personid, lastname, firstname, birthdate, deathdate, gedcom, @Years := year( @adt := if( deathdatetr, replace( deathdatetr, '-00', '-01' ) , curdate( ) ) ) - year( @abd := replace( birthdatetr, '-00', '-01' ) ) - ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) AS Years, @Months := ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) *12 + month( @adt ) - month( @abd ) - ( day( @adt ) < day( @abd ) ) AS Months, @Days := day( @adt ) - day( @abd ) + ( day( @adt ) < day( @abd ) ) * day( last_day( @adt - INTERVAL 1
MONTH ) ) AS Days, @ca := ( birthdatetr != @abd
OR (
deathdatetr != @adt
AND NOT living
) ) AS about, concat( convert( @ay , char ) , 'y, ', convert( @am , char ) , 'm, ', convert( @ad , char ) , if( @ca , 'd (about)', 'd' ) ) AS Age, living
FROM tng_people
WHERE gedcom = 'savenije' and deathdate != "y"
AND birthdatetr
AND (
deathdatetr
OR living
)
ORDER BY Years DESC , Months DESC , Days DESC , lastname, firstname 
5 267  Age in Years, Weeks, Days,    SELECT personid, lastname, firstname, birthdate, deathdate,gedcom,
@years := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -
year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as years,
@months := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as months, @days := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as days,
@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,
concat(convert(@years,char),' year, ',convert(@months,char),' months, ',convert(@days,char), if(@ca,'d (about)',' days')) as Age, living
FROM tng_people where (gedcom = 'savenije' and deathdate != "y" or deathdatetr != "0000-00-00") and birthdatetr and (deathdatetr or living)
order by Years desc, Months desc, Days desc,lastname, firstname 
6 191  Age of people at the beginning of WW2 (1940) eligable to fight  Leeftijd van mannen aan het begin van de tweede wereld oorlog. Konden ze in het leger of niet.
 
SELECT p.personID, p.lastname, p.firstname, et.description AS Conflict, 1940 - YEAR( p.birthdatetr ) AS age_at_beginning_of_world_war_two, e.eventdate AS Event_Date, e.eventplace AS Event, p.birthdate, p.deathdate, p.living
FROM tng_people AS p
LEFT OUTER JOIN tng_events AS e ON ( p.personID = e.persfamID
AND p.gedcom = e.gedcom )
LEFT OUTER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
WHERE birthdatetr <>0000 -00 -00
AND ( 1940 - YEAR( birthdatetr ) >=18 )
AND ( 1940 - YEAR( birthdatetr ) <=40 )
AND YEAR( deathdatetr ) >1940
AND sex = "M"
AND (
birthdate NOT LIKE "Aft%"
)
AND (
(
(
et.tag = "EVEN"
AND description LIKE "Mili%"
)
OR (
et.tag = "EVEN"
AND et.description = "Civil War"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Revolutionary%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "WWI%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Vietnam%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Korean%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "War of 1812%"
)
)
OR et.tag IS NULL
)
ORDER BY age_at_beginning_of_world_war_two,p.lastname, p.firstname, p.personID 
7 152  Age of people when they died  leeftijd van personen ten tijde van overlijden
Similar to the report 124 only now it gives ages with the addition of months and days.
Hetzelfde als rapport 124 alleen geeft het nu ook de maanden en dagen 
SELECT personid, last_name, first_name, birth_date, death_date, concat( ay, 'y, ', am, 'm, ', ad, if( around, 'd (around)', 'd' ) ) AS age, living, gedcom
FROM (

SELECT personid, last_name, first_name, birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) AS ay, (
mid( adt, 6, 5 ) < mid( abd, 6, 5 )
) *12 + month( adt ) - month( abd ) - ( day( adt ) < day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) < day( abd ) , day( last_day( adt - INTERVAL 1
MONTH ) ) , 0 ) AS ad, (
birth_date != abd
OR (
death_date != adt
AND living
)
) AS around, living, gedcom
FROM (

SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, if( day( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abd, if( deathdatetr, if( day( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adt, living, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr
OR living
)
) AS ppl
) AS agp
ORDER BY ay DESC , am DESC , ad DESC , last_name, first_name 
8 153  Ages of people when they died  Leeftijden van overleden personen  SELECT personid, last_name, first_name, birth_date, death_date, age, months, days, approx, living, gedcom
FROM (

SELECT personid, last_name, first_name, birth_date, death_date, year( adeath_date ) - year( abirth_date ) - ( mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 ) ) AS age, (
mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 )
) *12 + month( adeath_date ) - month( abirth_date ) - ( DAY( adeath_date ) < DAY( abirth_date ) ) AS months, DAY( adeath_date ) - DAY( abirth_date ) + if( DAY( adeath_date ) < DAY( abirth_date ) , DAY( last_DAY( adeath_date - INTERVAL 1
MONTH ) ) , 0 ) AS days, (
birth_date != abirth_date
OR (
death_date != adeath_date
AND living
)
) AS approx, living, gedcom
FROM (

SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, living, if( DAY( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abirth_date, if( deathdatetr, if( DAY( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adeath_date, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr <> "0000-00-00"
OR living
)
) AS ppl
) AS agp
ORDER BY age DESC , months DESC , days DESC , last_name, first_name 
9 45  all occuring places, including place levels  all occuring places, including place levels   SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place;  
10 132  all occuring second place name levels p, including frequency,  all occuring second place name levels, including frequency, ordered by place name level

Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen,geordend volgens plaatsnaam niveau 
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Level_2;  
11 133  all occuring second place name levels, including frequency, ordered by frequency  all occuring second place name levels, including frequency, ordered by frequency

Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie 
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Number DESC, Level_2; 
12 134  All occuring third place levels, including frequency, ordered by place level  All occuring third place name levels, including frequency, ordered by place name level

Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend bij plaatsnaam niveau.  
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Level_3;  
13 135  All occuring third place name levels, including frequency, ordered by frequency  All occuring third place name levels, including frequency, ordered by frequency

Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie 
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Number DESC, Level_3;  
14 293  All people with their spouse and parents  Alle mensen met hun partner en ouders.   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; 
15 260  All wrong dates  Alle foutieve datums  SELECT personID, lnprefix, lastname, firstname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr, changedby, gedcom FROM tng_people

WHERE (Length( deathdate ) >4 AND NOT ( deathdate LIKE "Abt%" OR deathdate LIKE "Cal %" OR deathdate LIKE "Bef %" OR deathdate LIKE "Aft %" OR deathdate LIKE "Est %" OR deathdate LIKE "Bet %" OR deathdate LIKE "% BC" ) AND deathdatetr LIKE "%-00-00") OR

(Length( birthdate ) >4 AND NOT ( birthdate LIKE "Abt%" OR birthdate LIKE "Cal %" OR birthdate LIKE "Bef %" OR birthdate LIKE "Aft %" OR birthdate LIKE "Est %" OR birthdate LIKE "Bet %" OR birthdate LIKE "% BC" ) AND birthdatetr LIKE "%-00-00") OR

(Length( altbirthdate ) >4 AND NOT ( altbirthdate LIKE "Abt%" OR altbirthdate LIKE "Cal %" OR altbirthdate LIKE "Bef %" OR altbirthdate LIKE "Aft %" OR altbirthdate LIKE "Est %" OR altbirthdate LIKE "Bet %" OR altbirthdate LIKE "% BC" ) AND altbirthdatetr LIKE "%-00-00") OR

(Length( burialdate ) >4 AND NOT ( burialdate LIKE "Abt%" OR burialdate LIKE "Cal %" OR burialdate LIKE "Bef %" OR burialdate LIKE "Aft %" OR burialdate LIKE "Est %" OR burialdate LIKE "Bet %" OR burialdate LIKE "% BC" ) AND burialdatetr LIKE "%-00-00") OR

(Birthdate like "%-%" or deathdate like "%-%" or altbirthdate like "%-%" or burialdate like "%-%") or

(Birthdate like "%?%" or deathdate like "%?%" or altbirthdate like "%?%" or burialdate like "%?%") or

(Birthdate like "%.%" or deathdate like "%.%" or altbirthdate like "%.%" or burialdate like "%.%") or

(NOT ( ( HEX( birthdate ) REGEXP '^([0-7][0-9A-F])*$' ) OR ( HEX( deathdate ) REGEXP '^([0-7][0-9A-F])*$' ) )) or

(Birthdate like "%/%" or deathdate like "%/%" or altbirthdate like "%/%" or burialdate like "%/%"); 
16 305  Alle email adressen    SELECT email
FROM `tng_users`  
17 239  Associations between people  Verbindingen tussen personen  SELECT p.personID, p.lastname AS lastname1, p.firstname AS firstname1, p.birthdate AS born1, p.living AS living1, p.gedcom, a.relationship AS Association, p2.personID AS Person2, p2.lastname AS lastname2, p2.firstname AS firstname2, p2.birthdate AS born2, p2.living AS living2, p2.gedcom
FROM tng_ass AS a
LEFT JOIN tng_people AS p ON ( a.personID = p.personID
AND a.gedcom = p.gedcom )
LEFT JOIN tng_people AS p2 ON ( a.passocID = p2.personID
AND a.gedcom = p2.gedcom )
WHERE p.living <>1
AND p2.living <>1
ORDER BY p.lastname, p.firstname, p.birthdatetr 
18 122  birthday to death, one = equals 10 people  individuals: frequency distribution of days from birthday to death, one = equals 10 people
Individuen: grafiek van de verdeling van dagen tussen verjaardig en overlijden, een = is 10 mensen 
SELECT IF(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))< 184,
TRUNCATE(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))/7,0),
TRUNCATE((366-ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr)))/7,0))
AS Difference_in_weeks, COUNT(*) AS Number, RPAD('',COUNT(*)/5,'=') AS Statistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks;  
19 27  Boogschutter  Personen, geboren in het astrologische teken Boogschutter (Sagittarius): 23.11.-21.12.
Individuals born in astrological sign sagittarius: 23 NOV - 21 DEC Boogschutter 22 november - 21 december Ik wil recht

Oordeels- en meningsvorming
Hogere studies. Het recht. Het buitenland
Idealen, Religie. Vrijheidsdrang
Heerser Jupiter
- doet je zoeken naar de samenhang der dingen, door je steeds weer het waarom van de dingen
af te vragen en zo tot nieuwe inzichten te komen en die kennis dan uit te dragen
- maakt je bewust van datgene wat jij als de waarheid ziet
Sagittarius is the Mutable-Fire sign. Restless energy and the need for personal independence keeps Sagittarians moving in many directions. They become experts at adapting to whatever culture and clime happens to fit their current interest. Always ready to travel for business or pleasure, and sometimes because of an overwhelming urge to escape (either figuratively or literally), they are all too willing to bypass the confinements of responsibility and work. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21)) ORDER BY lastname, firstname, personID; 
20 155  Born after Baptized  Persons who are born after they are baptized
Personen die geboren zijn nadat ze gedoopt zijn. 
SELECT personID, firstname, lastname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, YEAR( altbirthdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby
FROM tng_people
WHERE (
altbirthdatetr - birthdatetr <0
)
AND (
`birthdatetr` !=0000 -00 -00
OR YEAR( altbirthdatetr ) !=0000
)
AND birthdate != ""
AND altbirthdate != ""
AND `living` = "0"
AND altbirthdate != "n"
AND altbirthdatetr - birthdatetr !=0 
21 34  Changed families  Gezinnen die verandert zijn in de laatste 90 dagen
Families changed within the last 90 days 
SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=f.changedate ORDER BY changedate DESC; 
22 36  Changed headstones with links to cemetries  Headstones: changes within the last 90 days (listing *without* linked individuals but *with* linked cemeteries)   SELECT mediaID, description, hs.notes, hs.changedate, cemname, city, county, state, country
FROM tng_media AS hs
LEFT JOIN tng_cemeteries AS cem ON cem.cemeteryID = hs.cemeteryID
WHERE hs.mediatypeID = "headstones"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
ORDER BY hs.changedate, description DESC  
23 31  Changed persons in the last 90 days  Lijst van personen waarin veranderingen en of toevoegingen zijn gedaan in de laatste 90 dagen, gesorteerd aflopend op de veranderdatum
List of the the people which changed the last 90 days, sorted on the last change date 
SELECT personID, lastname, firstname AS Name, birthdate, birthplace, deathdate, changedate, gedcom, living FROM tng_people WHERE
DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=changedate ORDER BY changedate DESC 
24 37  Changed photos, without links to people  Photos changed within the last 90 days (listing *without* linked individuals)  SELECT description, m.notes, m.changedate
FROM tng_media AS m
WHERE m.mediatypeID = "photos"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= m.changedate
ORDER BY m.changedate DESC  
25 283  Changes by Margje  Veranderingen door Margje  SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "Margje"
ORDER BY `tng_people`.`changedate` DESC 
26 35  Changes in headstones (Last 90 days w.o. people  headstones: changes within the last 90 days (listing *without* linked individuals and *without* linked cemeteries)  SELECT mediaID, description, notes, changedate
FROM tng_media AS hs
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
AND hs.mediatypeID = "headstones"
ORDER BY hs.changedate DESC  
27 33  Changes in histories with people  Veranderde documenten, levensverhalen MET links naar de personen
Documents/histories changed within the last 90 days (listing *with* linked individuals)  
SELECT dc.mediaID, description, notes, p.personID, p.lastname, p.firstname, dc.changedate, p.living, p.gedcom
FROM tng_media AS dc
LEFT JOIN tng_medialinks AS dcl ON dc.mediaID = dcl.mediaID
LEFT JOIN tng_people AS p ON dcl.personID = p.personID
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= dc.changedate
AND dc.mediatypeID = "histories"
ORDER BY dc.changedate DESC  
28 238  Changes made by users  Veranderingen door gebruikers aangemaakt Een = is 100 veranderingen  SELECT changedby AS changed_by, COUNT( * ) AS Total_number, RPAD( '', COUNT( * ) /100, '=' ) AS Graph
FROM tng_people
GROUP BY changed_by
ORDER BY total_number DESC 
29 188  Children born after 9 months after their father's death  Kinderen geboren later dan 9 maanden na hun vader's dood  SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname, p.living, father.personID
AS FatherNr, father.birthdate AS FatherBirthdate,
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age,
father.deathdate as Father_death, p.birthdate as cBirthdate,
CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), " Months")
AS dif_month, p.deathdate, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )

WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND p.deathdatetr <> "0000-00-00"
AND father.deathdatetr <> "0000-00-00"
AND

DATEDIFF(p.birthdatetr,father.deathdatetr) > 360

ORDER by cBirthdate, cLastname, cFirstname, dif_month 
30 262  Children born after mother is buried  Kinderen geboren nadat moeder begraven is  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID
AS FatherNr, father.birthdate AS FatherBirthdate,
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate,
YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age,
mother.burialdate, p.gedcom, p.changedby FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON
( father.personID = f.husband AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE
p.birthdatetr <> "0000-00-00" AND
mother.birthdatetr <> "0000-00-00" AND
p.deathdatetr <> "0000-00-00" AND
mother.burialdatetr <> "0000-00-00"
AND
mother.burialdatetr< p.birthdatetr
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr 
31 187  Children born after the death of their mother  Kinderen geboren na de dood van hun mother  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, mother.deathdate, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.deathdatetr <> "0000-00-00"
AND mother.deathdatetr <> "0000-00-00"
AND mother.deathdatetr < p.birthdatetr

ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr; 
32 186  Children born before their father  Kinderen geboren voor hun vader  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND father.birthdatetr > p.birthdatetr
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr; 
33 185  Children born before their mother  Kinderen geboren voor hun mother  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND mother.birthdatetr > p.birthdatetr
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr; 
34 168  Children born with parents younger than 15 or mother older than 49  Children born with either one of the parents younger than 15 or with a mother older than 49. I think 52 is the oldest reported mother I found so anything older must be wrong (at least more than 50 years ago)

Kinderen geboren uit ouders die jonger zijn dan 15 of uit een moeder die ouder was dan 49. 52 jaar is de oudste gedocumenteerde moeder die ik vond, dus de oudere moeders moeten fout zijn.  
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, ch.frel as rel_to_dad, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, ch.mrel as rel_to_mom, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.changedby, p.gedcom
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.birthdate NOT LIKE "Aft%"
AND (YEAR(father.birthdatetr) > 1700 OR YEAR(mother.birthdatetr) > 1700)
AND (YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) < 15 OR YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) > 49 OR YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) < 15)
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr; 
35 308  Children born within 9 months of each other  Kinderen geboren binnen 9 maanden na elkaar, als het tweede kind na meer dan 210 dagen is geboren en niet oud werd dan ging het om een zevenmaands kindje, 3 dagen na elkaar schijnt ook nog te kunnen, anders is het onwaarschijnlijk.   SELECT p1.living, p1.personID, p1.lastname AS Last_Name, p1.firstname AS Given_Name, p1.birthdate AS Birth_Date, p1.changedby, p1.edituser as editor1, p2.living, p2.personID AS ID2, p2.firstname AS Given_Name2, IF( p1.lastname <> p2.lastname, p2.lastname, "" ) AS Last_Name2, p2.birthdate AS Birth_Date2,p1.changedby, p2.edituser as editor2, ABS( DATEDIFF( p1.birthdatetr, p2.birthdatetr ) ) AS days, year(p2.deathdatetr)- year(p2.birthdatetr) as 2nd_age, p1.gedcom
FROM tng_children AS c1
LEFT JOIN tng_people AS p1 ON ( p1.personID = c1.personID
AND p1.gedcom = c1.gedcom )
LEFT JOIN tng_children AS c2 ON ( c2.familyID = c1.familyID
AND c2.gedcom = c1.gedcom )
LEFT JOIN tng_people AS p2 ON ( p2.personID = c2.personID
AND p2.gedcom = c1.gedcom
AND p1.birthdatetr < p2.birthdatetr )
WHERE p1.birthdatetr <> "0000-00-00"
AND p2.birthdatetr <> "0000-00-00"
AND ABS( DATEDIFF( p1.birthdatetr, p2.birthdatetr ) ) <273
AND ABS( DATEDIFF( p1.birthdatetr, p2.birthdatetr ) ) > 2
AND p1.personID <> p2.personID
AND year(p2.deathdatetr)- year(p2.birthdatetr) != 0
ORDER BY days, p1.lastname 
36 259  Children with a different Metaphone than their father  Kinderen met een andere metafoon dan hun vader. Omdat achternamen soms een weinig in spelling verschillen wilde ik die uitsluiten en in die zin zouden er minder namen in moeten staan.

Because the spelling of a surname is sometimes slightly different but the sound isn't, I wanted to make a report which compared the sound of the last name of the children with the sound of the last name of the father 
SELECT p.personID, p.lnprefix, p.lastname, p.firstname, p.birthdate, p.living, p.metaphone, p.gedcom, f.familyID, father.personID AS FatherNr, father.lnprefix, father.lastname AS Fatherlast_name, father.metaphone as fathermetaphone, mother.personID AS MotherNr, mother.metaphone AS mothermetaphone, mother.lastname AS Motherlast_name, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE (dm(p.lastname) <> dm(father.lastname)) AND (dm(p.lastname) <> dm(mother.lastname))
AND YEAR( p.birthdatetr ) > "1811"
ORDER BY p.lastname, p.firstname, p.birthdatetr 
37 209  Couples having the same names  Partners die dezelfde namen hebben  SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom 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)
WHERE (h.lastname=w.lastname) and (h.firstname=w.firstname)
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;  
38 261  Couples of whom at least one were born or died in the peat colonies  Lijst van echtparen waarvan er minstens een van hen in de Kanaalstreken of monden is geboren of gestorven  SELECT

familyID,

h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, h.birthdate as birthdate1, h.birthplace as birthplace1, h.deathdate as deathdate1, h.deathplace as deathplace1,

w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2,
w.birthdate, w.birthplace, w.deathdate, w.deathplace

FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.birthplace LIKE "%kanaal%"
OR w.birthplace LIKE "%kanaal%"
)
OR (
h.birthplace LIKE "%mond, %Drenthe"
OR w.birthplace LIKE "%mond, %Drenthe"
)
OR
(
h.deathplace LIKE "%kanaal%"
OR w.deathplace LIKE "%kanaal%"
)
OR (
h.deathplace LIKE "%mond, %Drenthe"
OR w.deathplace LIKE "%mond, Drenthe%"
)
OR
(
h.altbirthplace LIKE "%kanaal%"
OR w.altbirthplace LIKE "%kanaal%"
)
OR (
h.altbirthplace LIKE "%mond, %Drenthe"
OR w.altbirthplace LIKE "%mond, %Drenthe"
)

OR

(
h.burialplace LIKE "%kanaal%"
OR w.burialplace LIKE "%kanaal%"
)
OR (
h.burialplace LIKE "%mond, %Drenthe"
OR w.burialplace LIKE "%mond, %Drenthe"
)

OR
(
h.birthplace LIKE "%Nieuw-Buinen%"
OR w.birthplace LIKE "%Nieuw-Buinen%"
)
OR
(
h.deathplace LIKE "%Nieuw-Buinen%"
OR w.deathplace LIKE "%Nieuw-Buinen%"
)

OR
(
h.birthplace LIKE "%Ter Apel%"
OR w.birthplace LIKE "%Ter Apel%"
)
OR
(
h.deathplace LIKE "%Ter Apel%"
OR w.deathplace LIKE "%Ter Apel%"
)
OR
(
h.birthplace LIKE "%Veendam%"
OR w.birthplace LIKE "%Veendam%"
)
OR
(
h.deathplace LIKE "%Veendam%"
OR w.deathplace LIKE "%Veendam%"
)
OR
(
h.birthplace LIKE "%Wildervank%"
OR w.birthplace LIKE "%Wildervank%"
)
OR
(
h.deathplace LIKE "%Wildervank%"
OR w.deathplace LIKE "%Wildervank%"
)

OR
(
h.birthplace LIKE "%Pekela%"
OR w.birthplace LIKE "%Pekela%"
)
OR
(
h.deathplace LIKE "%Pekela%"
OR w.deathplace LIKE "%Pekela%"
)

OR
(
h.birthplace LIKE "%Horsten%"
OR w.birthplace LIKE "%Horsten%"
)
OR
(
h.deathplace LIKE "%Horsten%"
OR w.deathplace LIKE "%Horsten%"
)

ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID
 
39 124  Dagen verschil tussen dood en leven  / Individuals: frequency distribution of difference (in "absolute" weeks) between day/month of birth and day/month of death   SELECT IF(ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)) < 27,
ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)),53-ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)))
AS Diffence_in_weeks, COUNT(*) AS Amount, RPAD('',COUNT(*)/5,'=') AS
Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND
DAYOFYEAR(deathdatetr)<>0 GROUP BY Diffence_in_weeks ORDER BY Diffence_in_weeks 
40 125  DE WET VAN TWAALF 

You can replace this with any thing you want or even just delete it




SAMENVATTING.



Hoe de 12 tekens van de dierenriem samenwerken en in elkaar grijpen, wordt hieronder nog eens samengevat in een concreet voorbeeld.



Er zal ergens een vereniging worden opgericht, bij voorbeeld tot steun van zieke, arme mensen.



Het initiatief ertoe wordt genomen door de heer Ram, die vol zit met ideeën en van zijn voornemen kennis geeft, door middel van een advertentie in de krant van de heer Tweelingen. Wie sympathiseert met het plan mag zijn kaartje sturen aan het bureau van het blad. De twee eerste reflectanten zijn de heer Stier en mevr. Kreeft. Stier, de investeerder heeft allang gevonden, dat er iets voor die arme drommels gedaan moest worden en hij is blij, dat iemand de kat de bel aanbindt. Hij stuurt zijn kaartje met het bericht erop, dat hij graag wil bijdragen in eventueel te maken onkosten. Kreeft, die elke dag een emotie nodig heeft, zat zich juist te vervelen en grijpt gauw de gelegenheid aan, om zich eens "wezenlijk verdienstelijk" te maken. Hulp bieden aan arme mensen! "Die zijn vaak zo interessant".



Zo maken deze vier de periode van ontstaan mee en vormen de voorlopige commissie van bestuur. Zij roepen de andere gegadigden samen en nu wordt de vereniging geconstitueerd. De heer Ram, die de zaak op gang bracht, heeft het zijne gedaan en trekt zich voor het



presidium terug ten behoeve van de heer Leeuw, die zich in de voorzitterszetel wonder goed op zijn plaats voelt. Als zijn rechterhand en steun wordt tot secretaris gekozen de precieze en handige heer Maagd, die van de heer Tweelingen het voorlopige secretariaat en de correspondentie overneemt. Tot vice-voorzitter kiest men de welwillende en tactvolle mevrouw Weegschaal, die als een zachte sordino, de forse autocratische tonen van de president dempt. De beide overige leden van het voorlopige bestuur nemen ook zitting in het definitieve, de heer Stier, als penningmeester en mevr. Kreeft als algemeen adjunct.



Voor haar moet de pret nog beginnen. Stier blijft altijd zitten waar hij zit.



Nu is de vereniging er, en het lid, dat dadelijk principieel in de oppositie is, is de heer Schorpioen, die op de voorgrond stelt, dat het allereerst nodig is, te erkennen dat armoede niet moest bestaan en de samenleving, waarin die aanwezig is, fout is en dat het heel aardig is voor rijke mensen (met een schuin oog op de heer Stier) om een beetje weldoenertje te spelen, als ze zelf meer dan genoeg hebben. Dat het ook gemakkelijk is, royaal de grote heer te spelen (met een blik op de heer Leeuw) maar dat het niet zo gemakkelijk is te leven naar: “al het mijn
is het uwe.”



Dan komt de tijd om voor de vereniging om propaganda te maken, wat door de heer Boogschutter met verve gedaan wordt. En ondertussen gaat het werk beginnen. Armen moeten worden bezocht, verzoeken om steun onderzocht, enz. Dat is werk voor de heer Steenbok, die altijd nog tijd vindt de ondankbare baantjes op te knappen en steeds zwoegend in touw is. Bij officiële gelegenheden, ontvangst ten stadhuize, optochten, mag deze heer het vaandel dragen, terwijl de heer Leeuw de erewijn opdrinkt.



Met het optreden van de heer Waterman begint het contact zoeken met zusterverenigingen, die hetzelfde doel nastreven en zo mogelijk wordt er een federatief verband gesloten. Tenslotte zijn er geen armen meer, dank zij het werk van de vereniging en in de laatste vergadering wordt de heer Vis benoemd tot liquidateur. Wat met Ram begint; eindigt met Vissen.



Zo onderscheiden wij duidelijk in elke vereniging drie perioden:



1. Een leider met een groep volgelingen, dit om leiding vragen, de vorm
is vaag en de leider is geen verantwoording verschuldigd.



2. Een bestuur gekozen uit en door de leden. Er is een reglement nodig en
dus is het bestuur verantwoording schuldig.



3. De propagandisten krijgen de leiding en voor het werk wordt een
betaalde beambte genomen. De vereniging oriënteert zich intercommunaal of internationaal en verdwijnt tenslotte.



In de eerste periode dreigt het gevaar van Kreeft, die door de overdrevenheid en emotionaliteit een verkeerd oordeel over de beweging doet ontstaan en daardoor velen afschrikt.



In de tweede periode dreigt het gevaar van Schorpioen, die met zijn principes alle opportunisme vergeet en hevige inwendige strubbelingen veroorzaakt.



In de derde periode werkt alles mee tot verval. Feitelijk is dit het tijdperk dat de vereniging zich "te buiten gaat". Wij moeten dit in filosofische zin nemen, maar letterlijk geschiedt het ook zo. De krachtige propaganda doet een grote uitbreiding ontstaan en de begeerte ontstaat
naar een eigen tehuis. Dit wordt door Steenbok verwezenlijkt. Hiermede is de vereniging op haar toppunt en begint nu af te zakken. De afstand tussen het hoofdbestuur, zetelend in het tehuis, en de leden wordt steeds groter. Leeuw, de autocraat, wordt uiteindelijk vervangen door Waterman, de democratische, die een nieuw tijdperk van schijnbare uitbreiding brengt door de congressen en banden met zusterverenigingen. Doch Vissen, de opvolger van Schorpioen, in het bestuur is niet in staat nieuwe animo te wekken.



Slap beleid en verwaarlozing van uiterlijk decorum doen de beweging steeds verder achteruitgaan, totdat het besluit tot opheffing als een verlossing komt.



En zo zien we in de bestuurswisselingen de groei weerspiegeld. In de aanvang een minimum aan bestuursleden. Eigenlijk één leider, die vanzelf voorzitter is: Ram, geassisteerd door 2 leden, de toegewijde Stier, die alles betaalt en de kritiekloze Tweelingen, die alle correspondentie afdoet.



In de tweede periode: een volledig bestuur: Leeuw, Weegschaal, Stier, Maagd en Kreeft. Na enige tijd is voor Kreeft, het nieuwtje eraf en men benoemt de opposant Schorpioen in zijn plaats, hopende hem daardoor te temmen. Nu is er voortdurend strijd tussen Leeuw en Schorpioen in het bestuur. Weegschaal tracht te bemiddelen; als dit faalt, vertrekt zij en wordt vervangen door de fanatieke Boogschutter, die nu het hoogste woord krijgt en daardoor Leeuw doet opstappen. Nu wordt Boogschutter voorzitter en in plaats van Weegschaal doet Waterman zijn intrede als vice-voorzitter. Maagd is dan aan de beurt om te verdwijnen en wordt vervangen door Steenbok, de betaalde ambtenaar.



Schorpioen houdt op te vechten tegen de luidruchtigheid van Boogschutter en het formalisme van Steenbok. Hij verklaart de beweging voor dood en verlaat met zijn aanhangers het strijdperk. In zijn plaats komt Vissen.



Achtereenvolgens worden Steenbok, Waterman en Vissen het meest invloedrijk. De enige die er van het begin tot het einde in blijft is Stier, die er zijn goede geld in gestoken heeft en uit de desolate boedel redt, wat er te redden valt.


 
SELECT * FROM `tng_places` WHERE `place`=NULL; 
41 165  Different surname as both parents  People whose last names is different from the last name of the father AND the last name of the mother.

Mensen met een andere achternaam dan de vader EN de mother.  
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Fatherlast_name, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname<>father.lastname AND p.lastname<>mother.lastname AND YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr;  
42 205  Distance between death place and the place burried (in Km)  Afstand tussen plaats van overlijden en begraven in kilometers   SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(deathcoords.latitude))*SIN(RADIANS(burialcoords.latitude))+COS(RADIANS(deathcoords.latitude))*COS(RADIANS(burialcoords.latitude))*COS(RADIANS(burialcoords.longitude-deathcoords.longitude))),1) AS Distance FROM tng_people AS p
LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom)
LEFT JOIN tng_places AS burialcoords ON (p.burialplace=burialcoords.place AND p.gedcom=burialcoords.gedcom)
WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" AND deathcoords.latitude<>"" AND deathcoords.longitude<>"" and burialcoords.latitude<>"" AND burialcoords.longitude<>""
ORDER BY Distance DESC, lastname, firstname, birthdatetr;  
43 212  Distance in kilometers between birth place and baptism place  Afstand in kilometers tussen de plaats van geboorte en de plaats van doop  SELECT personID, lastname, firstname, birthdate, birthplace, altbirthdate, altbirthplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(altbirthcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(altbirthcoords.latitude))*COS(RADIANS(altbirthcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS altbirthcoords ON (p.altbirthplace=altbirthcoords.place AND p.gedcom=altbirthcoords.gedcom) WHERE birthplace<>altbirthplace AND birthplace<>"" AND altbirthplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and altbirthcoords.latitude<>"" AND altbirthcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr 
44 211  Distance in kilometers between place of birth and place of death  Het verschil in kilometers tussen de plaats van geboorte en de plaats van overlijden  SELECT personID, lastname, firstname, birthdate, birthplace, deathdate, deathplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(deathcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(deathcoords.latitude))*COS(RADIANS(deathcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom) WHERE birthplace<>deathplace AND birthplace<>"" AND deathplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and deathcoords.latitude<>"" AND deathcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr 
45 32  Document changes  Veranderingen van de laatste 90 dagen in documenten, ZONDER de gelinkte personen.
Documents/histories changed within the last 90 days (listing *without* linked individuals)  
SELECT doc.mediaID, mediatypeID, description, notes, changedate
FROM tng_media AS doc
LEFT JOIN tng_medialinks AS documentlink ON doc.mediaID = documentlink.mediaID
WHERE (
DATE_SUB( CURDATE( ) , INTERVAL -90
DAY )
)
AND doc.mediatypeID = "documents"
ORDER BY doc.changedate DESC  
46 161  Documents linked to people not to an event  Documenten die aan een persoon gelinked zijn.   SELECT description, p.personID, p.gedcom, p.lastname, p.firstname, p.living,
p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND
ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE mediatypeID="documents" AND eventID=""
ORDER BY description;
 
47 258  Duplicate events for the same person  Dubbele gebeurtenissen voor individuen  SELECT e2.description, e1.info, e2.tag, e1.eventdate, e1.eventtypeID, e1.persfamID, count( * ) AS duplicated
FROM tng_events e1
INNER JOIN tng_eventtypes e2 ON e1.eventtypeID = e2.eventtypeID
GROUP BY e2.description, e1.eventtypeID, e1.persfamID
HAVING duplicated >1
ORDER BY e1.eventtypeID 
48 41  empty notes  empty notes  SELECT persfamID, note FROM tng_xnotes AS xn LEFT JOIN tng_notelinks AS nl ON
nl.xnoteID=xn.ID WHERE note REGEXP "[print]|[punct]|[\.]|
[\?]"=0 ORDER BY persfamID; 
49 106  Families sorted according to number of children    SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname AS surname, h.firstname AS christianname, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) WHERE h.personID<>"" GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren, f.familyID, w.personID, w.lastname AS surname, w.firstname AS christianname, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE w.personID<>"" GROUP BY w.personID
ORDER BY NumberOfChildren DESC, familyID, surname, christianname;  
50 84  families with missing partners  families with missing partners   SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) ORDER BY familyID;  
51 201  families with missing partners but WITH marriage date  families with missing partners but WITH marriage date  SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) AND marrdate <> "" ORDER BY familyID;
 
52 230  Families with only one spouse and no children  Gezinnen met met een partner en geen kinderen (dus verkeerd ingevoerde gezinnen)  SELECT f.familyid, f.husband AS Husband_ID, f.wife AS Wife_ID, f.marrdate, c.personID AS Child_ID, f.living, f.gedcom, changedby
FROM tng_families AS f
LEFT OUTER JOIN tng_children AS c ON c.familyID = f.familyID
WHERE (
(
f.husband LIKE 'I%' =0
)
OR (
f.husband = '-'
)
OR (
f.wife LIKE 'I%' =0
)
OR (
f.wife = '-'
)
)
AND c.personID IS NULL
ORDER BY c.personID, f.familyID 
53 112  families, ordered by husband's name  families, ordered by husband's name   SELECT familyID, h.personID AS EhemannPersonID, h.lastname AS Nachname1, h.firstname AS Vorname1, w.personID AS EhefrauPersonID, w.lastname AS Nachname2, w.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;  
54 113  families, ordered by wife's maiden name  families, ordered by wife's maiden name   SELECT familyID, w.personID AS EhefrauPersonID, w.lastname AS Nachname1, w.firstname AS Vorname1, h.personID AS EhemannPersonID, h.lastname As Nachname2, h.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID;  
55 196  families: couples with same last names  Gezinnen, stellen met dezelfde last_name  SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, w.personID AS WifePersonID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, f.living, f.gedcom 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)
WHERE h.lastname=w.lastname
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;  
56 99  families: frequency distribution of husband's marriage age, by 5-year-steps  families: frequency distribution of husband's marriage age, by 5-year-steps one = equals 50 people
Gezinnen: huwelijksgrafieken van de man's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen 
SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS age_of_marriage_since, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS age_of_marriage_till, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since;  
57 97  families: frequency distribution of husband's marriage age, by year  families: frequency distribution of husband's marriage age, by year one = equals 50 people
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de man, een = is 50 mensen 
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage;  
58 95  families: frequency distribution of marriage age, by year  families: frequency distribution of marriage age,
Gezinnen: huwelijksgrafieken per huwelijksleeftijd 
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age
UNION
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age ORDER BY Marriage_age;  
59 98  families: frequency distribution of wife's marriage age, by 5-year-steps  families: frequency distribution of wife's marriage age, by 5-year-steps one = equals 50 people
Gezinnen: huwelijksgrafieken van de vrouw's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen 
SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY married_age_from ORDER BY married_age_from;  
60 96  families: frequency distribution of wife's marriage age, by year  families: frequency distribution of wife's marriage age, by year one = equals 50 people
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de vrouw, een = is 50 mensen 
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_of_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage ORDER BY age_of_marriage;  
61 114  families: husbands  families: husbands   SELECT familyID, marrdate, h.personID, h.lastname, h.firstname, h.birthdate, h.living, h.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID ORDER BY h.lastname, h.firstname, h.personID;  
62 195  Families: husbands/wives, sorted by place of marriage  Gezinnen: mannen/vrouwen, gesorteerd naar plaats van de huwelijk  SELECT f.marrplace AS Marriage_place, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f
INNER JOIN tng_people AS p ON (f.husband=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.husband<>""
UNION
SELECT f.marrplace, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f
INNER JOIN tng_people AS p ON (f.wife=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.wife<>""
ORDER BY Marriage_place, lastname, firstname;  
63 130  families: individuals with father, but without mother (mother is missing)  Gezinnen met een missende mother  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_vader, father.lastname AS last_name, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS last_name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate;  
64 94  families: individuals with marriage date *after* death date  families: individual with marriage date *after* death date   SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr ORDER BY Number_of_years, lastname, firstname, personID;  
65 93  families: individuals with marriage date *before* birthdate  families: individuals with marriage date *before* birthdate   SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0 ORDER BY age_at_marriage, lastname, firstname, personID; 
66 117  Families: individuals with missing father or missing mother  Families: individuals with missing father or missing mother   SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.lastname AS Name1, father.firstname AS Firstname1, father.living, mother.personID AS MotherNr, mother.lastname AS Name2, mother.firstname AS Firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate 
67 89  families: marriage frequency by calendar month  families: marriage frequency by calendar month one = equals 50 people
Gezinnen: huwelijksgrafieken per kalendermaand, een = is 50 mensen 
SELECT MONTHNAME(marrdatetr) AS month_in_which_married, MONTH(marrdatetr) AS month_of_marriage_nr, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE MONTH(marrdatetr)>0 GROUP BY month_of_marriage_nr;  
68 87  families: marriage frequency by century  families: marriage frequency by century one = equals 100 people
Gezinnen: huwelijksgrafieken per eeuw, een = is 100 mensen 
SELECT 100*FLOOR(YEAR(marrdatetr)/100) AS since_year, 100*FLOOR(YEAR(marrdatetr)/100)+99 AS till_year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00'GROUP BY since_year ORDER BY since_year; 
69 90  families: marriage frequency by day-of-week  families: marriage frequency by day-of-week one = equals 50 people
Gezinnen: huwelijksgrafieken per dag van de week, een = is 50 mensen 
SELECT DAYNAME(marrdatetr) AS day_of_marriage, DAYOFWEEK(marrdatetr) AS number_of_the_week, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE DAYOFWEEK(marrdatetr)>0 GROUP BY number_of_the_week;  
70 88  families: marriage frequency by decades  families: marriage frequency by decades one = equals 10 people
Gezinnen: huwelijksgrafieken per eeuw, een = is 10 mensen 
SELECT 10*FLOOR(YEAR(marrdatetr)/10) AS since_year, 10*FLOOR(YEAR(marrdatetr)/10)+9 AS till_year, COUNT(*) AS Totals, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year;  
71 115  families: marriage types with individuals (with personIDs *and* names)  families: marriage types with individuals (with personIDs *and* names)   SELECT marrtype AS Type_of_connection, familyID, marrdate, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE f.marrtype<>'' ORDER BY f.marrtype, h.lastname, h.firstname, h.personID  
72 111  Families: Twins  Families: Twins   SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, p.gedcom FROM tng_children AS c INNER JOIN tng_people AS p ON p.personID=c.personID INNER JOIN tng_children AS c2 ON c2.familyID=c.familyID INNER JOIN tng_people AS p2 ON p2.personID=c2.personID WHERE (p2.birthdatetr=p.birthdatetr OR p2.birthdatetr=DATE_ADD(p.birthdatetr, INTERVAL 1 DAY) OR p2.birthdatetr=DATE_SUB(p.birthdatetr, INTERVAL 1 DAY)) AND YEAR(p.birthdatetr)<>0 AND MONTH(p.birthdatetr)<>0 AND DAYOFMONTH(p.birthdatetr)<>0 GROUP BY c.familyID, p.personID, p.birthdatetr HAVING COUNT(c2.familyID)=2 ORDER BY p.lastname, c.familyID, p.birthdatetr;  
73 110  Families: twins, triplets..  Families: twins, triplets..  SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT( c.familyID ) AS Number, p.gedcom
FROM tng_children AS c
INNER JOIN tng_people AS p ON p.personID = c.personID
INNER JOIN tng_children AS c2 ON c2.familyID = c.familyID
INNER JOIN tng_people AS p2 ON p2.personID = c2.personID
WHERE (
p2.birthdatetr = p.birthdatetr
OR p2.birthdatetr = DATE_ADD( p.birthdatetr, INTERVAL 1
DAY )
OR p2.birthdatetr = DATE_SUB( p.birthdatetr, INTERVAL 1
DAY )
)
AND YEAR( p.birthdatetr ) <>0
AND MONTH( p.birthdatetr ) <>0
AND DAYOFMONTH( p.birthdatetr ) <>0
GROUP BY c.familyID, p.personID, p.birthdatetr
HAVING COUNT( c2.familyID ) >=2
ORDER BY Number, p.lastname, c.familyID, p.birthdatetr 
74 116  families: wifes  families: wifes   SELECT familyID, marrdate, w.personID, w.lastname, w.firstname, w.birthdate, w.living, w.gedcom FROM tng_families AS f
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
ORDER BY w.lastname, w.firstname, w.personID;  
75 119  Faulty birth, baptism, death and burial APROXIMATE dates, e.g. abt1988  foute geschatte datums bijv, abt1988 (geen spatie)
Kijkt alleen naar geboorte, doop, overlijdens en begraaf datums.  
SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, living, gedcom, changedby FROM tng_people WHERE
(
(UCASE(birthdate) LIKE "%CAL%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%EST%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%ABT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%CAL%" AND altbirthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%EST%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%BEF%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%AFT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%ABT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%CAL%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%EST%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%BEF%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%AFT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%ABT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%CAL%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%EST%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%BEF%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%ABT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%AFT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00")
) AND year(birthdatetr) > "999"
ORDER BY lastname, firstname, personID;  
76 137  Faulty baptismal dates  foutieve doopdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, altbirthdate, altbirthdatetr, changedby
FROM tng_people
WHERE Length( altbirthdate ) >4
AND NOT (
altbirthdate LIKE "Abt%"
OR altbirthdate LIKE "Cal %"
OR altbirthdate LIKE "Bef %"
OR altbirthdate LIKE "Aft %"
OR altbirthdate LIKE "Est %"
OR altbirthdate LIKE "Bet %"
OR altbirthdate LIKE "% BC"
)
AND NOT (
altbirthdatetr LIKE "%-00-00"
OR (
altbirthdatetr LIKE "%-%-00"
)
OR altbirthdate LIKE "Feb %"
OR altbirthdate LIKE "Mar %"
OR altbirthdate LIKE "Apr %"
OR altbirthdate LIKE "May %"
OR altbirthdate LIKE "Jun %"
OR (
altbirthdate LIKE "Jul %"
OR altbirthdate LIKE "Aug %"
OR altbirthdate NOT LIKE "Sep %"
OR altbirthdate LIKE "Oct %"
OR altbirthdate LIKE "Nov %"
OR altbirthdate LIKE "Dec %"
)
77 136  Faulty birth dates  Foutieve geboortedatums  SELECT personID, lnprefix, lastname, firstname, birthdate, birthdatetr, gedcom, changedby FROM tng_people WHERE Length( birthdate ) >4 AND NOT ( birthdate LIKE "Abt%" OR birthdate LIKE "Cal %" OR birthdate LIKE "Bef %" OR birthdate LIKE "Aft %" OR birthdate LIKE "Est %" OR birthdate LIKE "Bet %" OR birthdate LIKE "% BC" ) AND birthdatetr LIKE "%-00-00"  
78 138  Faulty burial dates  foutieve begraafdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, burialdate, burialdatetr, changedby
FROM tng_people
WHERE Length( burialdate ) >4
AND NOT (
burialdate LIKE "Abt%"
OR burialdate LIKE "Cal %"
OR burialdate LIKE "Bef %"
OR burialdate LIKE "Aft %"
OR burialdate LIKE "Est %"
OR burialdate LIKE "Bet %"
OR burialdate LIKE "% BC"
)
AND burialdatetr LIKE "%-00-00" 
79 139  Faulty death dates  foutieve overlijdensdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, birthdate, birthdatetr, changedby
FROM tng_people
WHERE Length( birthdate ) >4
AND NOT (
birthdate LIKE "Abt%"
OR birthdate LIKE "Cal %"
OR birthdate LIKE "Bef %"
OR birthdate LIKE "Aft %"
OR birthdate LIKE "Est %"
OR birthdate LIKE "Bet %"
OR birthdate LIKE "% BC"
)
AND NOT (
birthdatetr LIKE "%-00-00"
OR (
birthdatetr LIKE "%-%-00"
)
OR birthdate LIKE "Feb %"
OR birthdate LIKE "Mar %"
OR birthdate LIKE "Apr %"
OR birthdate LIKE "May %"
OR birthdate LIKE "Jun %"
OR (
birthdate LIKE "Jul %"
OR birthdate LIKE "Aug %"
OR birthdate NOT LIKE "Sep %"
OR birthdate LIKE "Oct %"
OR birthdate LIKE "Nov %"
OR birthdate LIKE "Dec %"
)
80 190  Frequencies of origin of people  Frequencies van de geboorteplaats van mensen  SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(birthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY gemeente_or_state order by Number desc; 
81 270  Frequency of names  Frequentie van namen  SELECT P1.lastname,
CASE WHEN P1.lastname
IN (
'Savenije','Savonije','Savenay','Savigne','Savené','Saveneij','Saveney', 'Saveneals','Saveneal','Safney','Sauvenaij','Safneij','Savage')
THEN 'Savenije/Savonije/Savenay/etc'
WHEN P1.lastname
IN (
'Boekholt', 'Boekhout', 'Boekhoudt'
)
THEN 'Boekholt/Boekhout/Boekhoudt'
WHEN P1.lastname
IN (
'Muller', 'Mulder', 'Mulders','Mullers'
)
THEN 'Mulder/Muller/etc'
WHEN P1.lastname
IN (
'Meijer', 'Meier'
)
THEN 'Meijer/Meier/etc'
WHEN P1.lastname
IN (
'Jong', 'Jonge'
)
THEN 'de Jong/de Jonge/Jong'
WHEN P1.lastname
IN (
'Jans', 'Janse','Jansen','Janssen','Jansens','Janssens'
)
THEN 'Jans/Janse/etc'
WHEN P1.lastname
IN (
'Kruize', 'Kroese','Kroeze','Kruise'
)
THEN 'Kruize/Kroeze/etc'
WHEN P1.lastname
IN (
'Huizinga', 'Huizenga','Huisinga','Huisenga','Huijzinga','Huijsinga','Huijzenga','Huijsenga'
)
THEN 'Huizinga/Huisinga/etc'
WHEN P1.lastname
IN (
'Kruizinga', 'Kruizenga','Kruisinga','Kruisenga','Kruijzinga','Kruijsinga','Kruijzenga','Kruijsenga'
)
THEN 'Kruizinga/Kruisinga/etc'
WHEN P1.lastname
IN (
'Hendriks', 'Hindriks','Hendrixs','Hindrixs','Hendrix','Hindrix'
)
THEN 'Hendriks/Hindriks/etc'
WHEN P1.lastname
IN (
'Clercks', 'Clerx','Clerks'
)
THEN 'Clercks/Clerks/etc'
WHEN P1.lastname IN ( 'Smit', 'Smith', 'Smid' )
THEN 'Smit/Smith/Smid'
WHEN P1.lastname IN ( 'Drent', 'Drenth', 'Drente', 'Drenthe' )
THEN 'Drent/Drenth/Drenthe'
WHEN P1.lastname
IN (
'Visser', 'Visscher', 'Fisscher', 'Fisser'
)
THEN 'Visser/Visscher'
ELSE P1.lastname
END AS Surname, COUNT( * ) AS Frequency
FROM tng_people P1
WHERE P1.lastname LIKE '%'
AND NOT P1.lastname = "NN"
GROUP BY Surname
ORDER BY Frequency DESC
 
82 194  Frequency of people's marriage place  Frekwentie van plaatsen waar mensen getrouwd zijn  SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(marrplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrplace <> "" group BY provincie_or_country order by Number desc 
83 158  Husband is female  Marriages where the husband is female and therefore a mistake might have been made.
Huwelijken waar de man vrouwelijk is en er mogelijk een fout gemaakt is. 
SELECT f.familyID, h.personID, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.sex = "F"
)
ORDER BY familyID 
84 108  Incomplete families  Families where husband or wife is missing  SELECT familyid, husband AS EhemannPersonID, wife AS EhefrauPersonID, marrdate, living, gedcom FROM tng_families WHERE (husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-') ORDER BY familyID;  
85 109  Individuals (not: families!) with number of associated children  Individuals (not: families!) with number of associated children   SELECT COUNT(*) AS Number_of_children, f.familyID, h.personID, h.lastname AS Last_name, h.firstname AS First_name, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS h ON f.husband=h.personID WHERE h.personID<>"" GROUP BY h.personID UNION SELECT COUNT(*) AS Number_of_children, f.familyID, w.personID, w.lastname AS Last_name, w.firstname AS First_name, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE w.personID<>"" GROUP BY w.personID ORDER BY Number_of_children DESC, Last_name, First_name, familyID  
86 58  Individuals marked as living  Individuals marked as "living" with age > 100 years   SELECT personID, lastname, firstname, birthdate, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)>100) AND living=1 AND YEAR(birthdatetr)<>0 ORDER BY lastname, firstname, birthdatetr; 
87 91  individuals married with age <= 18 years  individuals married with age <= 18 years and marriage date AFTER 1785
(before 1785 there are too many people in the database who where married at a too young age, notably nobility) 
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE (f.marrdatetr-p.birthdatetr>0) AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) and YEAR(p.birthdatetr)>1785
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE (f.marrdatetr-p.birthdatetr>0) and YEAR(p.birthdatetr)>1785 AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) ORDER BY age_at_marriage, lastname, firstname, personID;  
88 92  individuals married with age >= 80 years  individuals married with age >= 80 years  SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 ORDER BY age_at_marriage, lastname, firstname, personID;  
89 246  individuals who died on their birthday  individuals who died at same day of month and month as they were born (without children died on day of birth)   SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(deathdatetr)-YEAR(birthdatetr) AS age, living, gedcom FROM tng_people WHERE DAYOFMONTH(birthdatetr)<>0 AND DAYOFMONTH(deathdatetr)<>0 AND MONTH(birthdatetr)<>0 AND MONTH(deathdatetr)<>0 AND DAYOFMONTH(birthdatetr)=DAYOFMONTH(deathdatetr) AND MONTH(birthdatetr)=MONTH(deathdatetr) AND YEAR(deathdatetr)-YEAR(birthdatetr)>0 ORDER BY lastname, firstname, birthdatetr; 
90 75  individuals with 100. birthdate this year or next year  individuals with 100. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=99 OR YEAR(CURDATE())-YEAR(birthdatetr)=100) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
91 67  individuals with 50. birthdate this year or next year  individuals with 50. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=49 OR YEAR(CURDATE())-YEAR(birthdatetr)=50) AND living=1 ORDER BY birthdatetr, lastname, firstname 
92 68  individuals with 60. birthdate this year or next year  individuals with 60. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=59 OR YEAR(CURDATE())-YEAR(birthdatetr)=60) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
93 69  individuals with 65. birthdate this year or next year  individuals with 65. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Jahre, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=64 OR YEAR(CURDATE())-YEAR(birthdatetr)=65) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
94 70  individuals with 70. birthdate this year or next year  individuals with 70. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=69 OR YEAR(CURDATE())-YEAR(birthdatetr)=70) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
95 71  individuals with 75. birthdate this year or next year  individuals with 75. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=74 OR YEAR(CURDATE())-YEAR(birthdatetr)=75) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
96 72  individuals with 80. birthdate this year or next year  individuals with 80. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=79 OR YEAR(CURDATE())-YEAR(birthdatetr)=80) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
97 73  individuals with 85. birthdate this year or next year  individuals with 85. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=84 OR YEAR(CURDATE())-YEAR(birthdatetr)=85) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
98 74  individuals with 90. birthdate this year or next year  individuals with 90. birthdate this year or next year   SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=89 OR YEAR(CURDATE())-YEAR(birthdatetr)=90) AND living=1 ORDER BY birthdatetr, lastname, firstname;  
99 60  individuals with an unclear date of birth  individuals with an unclear date of birth e.g. "ABT", "BEF", "AFT", "CAL"   SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((UCASE(birthdate) LIKE "%CAL%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%ERR%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%ABT%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate<>"") ) AND Birthdate <> "y"
ORDER BY lastname, firstname, personID;  
100 78  individuals with and unclear date of death  individuals with and unclear date of death   SELECT personID, lastname, firstname, deathdate, burialdate, gedcom FROM tng_people WHERE
((UCASE(deathdate) LIKE "%CA%") OR
(UCASE(deathdate) LIKE "%ERR%") OR
(UCASE(deathdate) LIKE "%VOR%") OR
(UCASE(deathdate) LIKE "%NACH%") OR
(UCASE(deathdate) LIKE "%ABT%") OR
(UCASE(deathdate) LIKE "%BEF%") OR
(UCASE(deathdate) LIKE "%AFT%") OR DAYOFMONTH(deathdate)=0 OR MONTH(deathdate)=0)
ORDER BY lastname, firstname, personID;  
101 222  individuals with associated notes   personen met geassocieerde notities  SELECT personID, lastname, firstname, birthdate, deathdate, living, note, p.gedcom FROM tng_people AS p
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom)
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE nl.secret=0
ORDER BY lastname, firstname, birthdatetr;  
102 245  individuals with different deathplace and place of burial   Personen die elders begraven zijn dan waar ze overleden.   SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, gedcom FROM tng_people WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" ORDER BY lastname, firstname, birthdatetr;  
103 129  individuals with mother, but without father (father is missing)  Individuen met hun mother maar waar de vader mist  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_Vader, father.lastname AS Name1, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS Name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.husband="" ORDER BY p.lastname, p.firstname, p.birthdate;  
104 54  individuals without date of birth/baptism/death/burial  individuals without date of birth/baptism/death/burial (empty date fields)   SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE
((birthdate is NULL) OR (birthdate="")) AND
(birthdatetr="0000-00-00") AND
((altbirthdate is NULL) OR (altbirthdate="")) AND
(altbirthdatetr="0000-00-00") AND
((deathdate is NULL) OR (deathdate="")) AND
(deathdatetr="0000-00-00") AND
((burialdate=NULL) OR (burialdate="")) AND
(burialdatetr="0000-00-00")
ORDER BY lastname, firstname;  
105 53  individuals without places  individuals without places - missing birth/baptism/death/burial place (empty place fields)   SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE ((birthplace=NULL) OR (birthplace="")) AND ((altbirthplace is NULL) OR (altbirthplace="")) AND ((deathplace=NULL) OR (deathplace="")) AND ((burialplace is NULL) OR (burialplace="")) ORDER BY lastname, firstname;  
106 77  individuals, by place of baptism  individuals, by place of baptism   SELECT birthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>""
UNION
SELECT altbirthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE altbirthplace<>""
ORDER BY Place_name, lastname, firstname;  
107 76  individuals, by place of birth  individuals, sorted by place of birth  SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" ORDER BY birthplace, lastname, firstname;  
108 17  Individuals, with their zodiacal sign  Een lijst van alle personen met hun sterrenbeeld
A list of all the people with their zodiacal sign 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Ram (Aries)" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Stier (Taurus)" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Tweelingen (Gemini)" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Kreeft (Cancer)" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leeuw (Leo)" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Maagd (Virgo)" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Weegschaal (Libra)" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Schorpioen (Scorpius)" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Boogschutter (Sagittarius)" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Steenbok (Capricornus)" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Waterman (Aquarius)" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Vissen (Pisces)" END AS Sterrenbeeld, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" ORDER BY lastname, firstname, personID; 
109 56  individuals: age frequency distribution  individuals: age frequency distribution (only deceased)
Individuen: leeftijdsgrafieken (alleen overledenen) 
SELECT YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, COUNT(YEAR(deathdatetr)-YEAR(birthdatetr)) AS Total FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age 
110 57  individuals: age frequency per decade  individuals: age frequency per decade (only deceased), one = equals 100 people
Individuen: leeftijdsverdeling per 10 jaren (alleen overledenen), een = is 100 mensen 
SELECT 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10) AS Age_From, 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10)+9 AS Age_To, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age_From;  
111 64  individuals: baptism frequency by century  individuals: baptism frequency by century, one = equals 100 people
Individuen: doopgrafieken per eew, een = is 100 mensen 
SELECT 100*FLOOR(YEAR(altbirthdatetr)/100) AS from_Year, 99+(100*FLOOR(YEAR(altbirthdatetr)/100)) AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year;  
112 63  individuals: birth frequency by calendar months  individuals: birth frequency by calendar months, one = equals 50 people
Individuen: geboortegrafieken per kalendermaand, een = is 50 mensen 
SELECT MONTHNAME(birthdatetr) AS Month_of_Birth, MONTH(birthdatetr) AS number_of_month_of_birth, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(birthdatetr)>0 GROUP BY number_of_month_of_birth;  
113 61  individuals: birth frequency by century  individuals: birth frequency by century, one = equals 100 people
Individuen: geboortegrafieken per eeuw, een = is 100 mensen 
SELECT 100*FLOOR(YEAR(birthdatetr)/100) AS Year_From, (100*FLOOR(YEAR(birthdatetr)/100))+99 AS Year_Till, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00'GROUP BY Year_From ORDER BY Year_From;  
114 83  individuals: birth frequency by day-of-week  individuals: birth frequency by day-of-week one = equals 50 people Individuen: geboorte grafieken per dag van de week, een = is 50 mensen  SELECT DAYNAME(birthdatetr) AS Name_of_birth_weekday , DAYOFWEEK(birthdatetr) AS Number_of_day_of_the_week, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE DAYOFWEEK(birthdatetr)>0 GROUP BY Number_of_day_of_the_week;  
115 62  individuals: birth frequency by decades  individuals: birth frequency by decades, one = equals 50 people
Individuen: geboortegrafieken per 10 jaren, een = is 50 mensen 
SELECT 10*FLOOR(YEAR(birthdatetr)/10) AS from_Year, (10*FLOOR(YEAR(birthdatetr)/10))+9 AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year;  
116 65  individuals: days between birth and baptism  individuals: number of days from birth and baptism
individuen: aantal dagen tussen geboorte en doop 
SELECT personID, lastname, firstname, birthdate, altbirthdate, TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS NumberTage, living, gedcom FROM tng_people WHERE birthdate<>"" AND altbirthdate<>"" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;  
117 243  individuals: death causes without names (including frequency)   Personen: doodsoorzaak zonder namen maar met frequentie  SELECT cause AS cause_of_death, COUNT( * ) AS total
FROM tng_events
WHERE cause <> ""
AND parenttag = "DEAT"
GROUP BY cause_of_death
ORDER BY cause_of_death; 
118 81  individuals: death frequency by calendar months  individuals: death frequency by calendar months one = equals 50 people
Individuen: overlijdensgrafieken per kalendermaand, een = is 50 mensen 
SELECT MONTHNAME(deathdatetr) AS name_of_month_of_death, MONTH(deathdatetr) AS number_of_death_month, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(deathdatetr)>0 GROUP BY number_of_death_month;  
119 79  individuals: death frequency by century  individuals: death frequency by century, one = equals 100 people

Individuen: overlijdensgrafieken per eeuw, een = is 100 mensen 
SELECT 100*FLOOR(YEAR(deathdatetr)/100) AS since_year, (100*FLOOR(YEAR(deathdatetr)/100))+99 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year; 
120 82  individuals: death frequency by day-of-week  individuals: death frequency by day-of-week one = equals 50 people
Individuen: overlijdensgrafieken per dag van de week, een = is 50 mensen 
SELECT DAYNAME(deathdatetr) AS name_of_day_of_death, DAYOFWEEK(deathdatetr) AS number_of_death_day, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFWEEK(deathdatetr)>0 GROUP BY number_of_death_day;  
121 80  individuals: death frequency by decades  individuals: death frequency by decades one = equals 20 people Individuen: overlijdensgrafieken per 10 jaar, een = is 20 mensen  SELECT 10*FLOOR(YEAR(deathdatetr)/10) AS since_year, (10*FLOOR(YEAR(deathdatetr)/10))+9 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/20,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year;  
122 247  individuals: events: alias names (not: nick names) with associated people, order  Personen die bekend waren onder een andere naam, dus geen bijnamen  SELECT p.personID, lastname, firstname, birthdate, deathdate, info AS also_known_as, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY lastname, firstname, p.personID;  
123 248  individuals: events: alias names (not: nick names) with associated people, order  Mensen die onder een andere naam bekend stonden, gesorteerd op de andere naam  SELECT info AS also_known_as, p.personID, lastname, firstname, birthdate, deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY info, lastname, firstname, p.personID;  
124 249  individuals: events: emigrated persons  Mensen die geemigreerd zijn.   SELECT p.personID, lastname, firstname, birthdate, deathdate, eventdate AS date_emigration, eventplace AS place_to_where, info AS reasons, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="EMIG" ORDER BY lastname, firstname, p.personID; 
125 250  individuals: events: occupations with names   Personen, beroepen en de naam en plaats van die beroepen  SELECT info AS description_of_occupation, eventdate AS date_, eventplace AS place_of_the_occupation, p.personID, lastname, firstname, birthdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" ORDER BY info, lastname, firstname, p.personID; 
126 251  individuals: events: occupations without names (including frequency)   Personen, beroepen zonder de naam van de persoon maar met de frequentie, geordend naar het beroep   SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY Occupation; 
127 252  individuals: events: occuring occupations ordered on frequency   Een lijst van beroepen gerangschikt naar beroepen.   SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY total DESC, Occupation;  
128 256  individuals: events: peoples with "empty" residences (check for data plausibilit  Personen waar de woonplaats leeg is, check op waarschijnlijkheid van voorkomen.   SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, e.eventplace AS dwelling_place, e.info AS additional_information, p.living, p.gedcom FROM tng_events AS e
LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
LEFT JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace="" ORDER BY p.lastname, p.firstname, p.personID; 
129 255  individuals: events: residences with associated names  Personen gerangschikt naar de plaats waar men woonde.   SELECT e.eventplace AS dwelling_place, e.info AS additional_information, p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace<>"" ORDER BY e.eventplace, p.lastname, p.firstname;  
130 253  individuals: farmers - with farmer's names, ordered by farmer's name   Mensen die landbouwer of boer waren. Gerangschikt op de namen van de boeren/landbouwers  SELECT info AS description_of_the_occupation, eventdate AS Dates, eventplace AS place_of_the_occupation, agency AS position, p.personID, lastname, firstname, birthdate, p.living, p.gedcom
FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID
AND e.gedcom = p.gedcom )
WHERE et.tag = "OCCU"
AND (
info LIKE "%boer%"
OR info LIKE "%bouwer%"
)
ORDER BY lastname, firstname, p.personID, info; 
131 254  individuals: farmers - with farmer's names, ordered by occupation  Boeren, gerangschikt op de omschrijving van het beroep  SELECT info AS description_of_the_occupation, eventdate AS Dates, eventplace AS place_of_the_occupation, agency AS position, p.personID, lastname, firstname, birthdate, p.living, p.gedcom
FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID
AND e.gedcom = p.gedcom )
WHERE et.tag = "OCCU"
AND (
info LIKE "%boer%"
OR info LIKE "%bouwer%"
)
ORDER BY info, lastname, firstname, p.personID; 
132 66  individuals: frequency distribution of days from birth to baptism  individuals: frequency distribution of days from birth to baptism, one = equals 10 people
Individuen: grafiek van de verdeling van dagen tussen geboorte en doop, een = is 10 mensen 
SELECT TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS Total_days, COUNT(*) AS Frequency, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_people WHERE altbirthdate<>"" AND birthdate<>"" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 GROUP BY Total_days ORDER BY Total_days;  
133 52  individuals: frequency distribution of zodiacal signs  individuals: frequency distribution of zodiac signs   SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Aries" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Taurus" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Gemini" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Cancer" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leo" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Virgo" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Libra" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Scorpius" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Sagittarius" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Capricornus" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Aquarius" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Pisces" END AS Sternzeichen, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" GROUP BY Sternzeichen;  
134 18  Individuals: frequency of zodiacal signs  Personen: Frekwentie van de sterrenbeelden  SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Ram (Aries)" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Stier (Taurus)" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Tweelingen (Gemini)" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Kreeft (Cancer)" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leeuw (Leo)" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Maagd (Virgo)" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Weegschaal (Libra)" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Schorpioen (Scorpius)" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Boogschutter (Sagittarius)" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Steenbok (Capricornus)" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Waterman (Aquarius)" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Vissen (Pisces)" END AS Sterrebeeld, COUNT(*) AS Aantal FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" GROUP BY Sterrebeeld;  
135 121  individuals: number of days between birth and death  individuals: number of days between birthday and death
individuen: aantal dagen tussen verjaardag en overlijden 
SELECT personID, lastname, firstname, birthdate, deathdate, ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) AS Number_of_days, living, gedcom FROM tng_people WHERE birthdate<>"" AND deathdate<>"" AND DAYOFMONTH(deathdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;
 
136 242  individuals: people with nicknames, ordered on nicknames  Mensen met bijnamen of roepnamen  SELECT nickname, personID, lastname, firstname, birthdate, deathdate, living, gedcom FROM tng_people
WHERE nickname<>"" ORDER BY nickname, lastname, firstname, personID; 
137 240  individuals: titles with corresponding names   individuals: titles without names - occurring titles with frequency   SELECT title, COUNT( * ) AS Total
FROM tng_people
WHERE title <> ''
GROUP BY title
ORDER BY title 
138 241  individuals: titles with corresponding names   individuals: titles with corresponding names   SELECT personID, title, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE title<>"" ORDER BY title, lastname, firstname, personID 
139 55  indivuals ordered by ascending age  indivuals ordered by ascending age (only deceased)   SELECT lastname, firstname, personID, birthdate, birthdatetr, deathdate, deathdatetr, YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, gedcom FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") ORDER BY Age, lastname, firstname  
140 309  Invalid baptismal dates  Foute doopdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, altbirthdate, altbirthdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( altbirthdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( altbirthdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( altbirthdate, '%Y' ) IS NULL
AND NOT (altbirthdate = "y" or altbirthdate = "n"
) and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)

and NOT (altbirthdate like "Bet %" and length (altbirthdate) = 17 and (year(altbirthdatetr) != 0000 or month(altbirthdatetr) != 00 or day(altbirthdatetr) != 00))
and NOT (altbirthdate like "Bet %" and length (altbirthdate) = 31 and (year(altbirthdatetr) != 0000 or month(altbirthdatetr) != 00 or day(altbirthdatetr) != 00))


ORDER BY `tng_people`.`altbirthdate` ASC 
141 59  inviduals: birthdays in the current month  inviduals: birthdays in the current month (only deceased persons)   SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(NOW())-YEAR(birthdatetr) AS Years, gedcom FROM tng_people WHERE MONTH(birthdatetr)=MONTH(NOW()) AND living=0 ORDER BY lastname, firstname, personID;  
142 294  Invisible characters in birth or death field  Onzichtbare karakters in geboorte of overlijdens veld  SELECT personID, gedcom, lastname, firstname, birthdate, birthdatetr, deathdate, deathdatetr
FROM tng_people
WHERE NOT (
(
HEX( birthdate ) REGEXP '^([0-7][0-9A-F])*$'
)
OR (
HEX( deathdate ) REGEXP '^([0-7][0-9A-F])*$'
)
)
ORDER BY `personID` DESC  
143 22  Kreeft  Personen, geboren in het astrologische teken kreeft (Cancer): 22 juni-22 juli
Individuals born in astrological sign cancer: 22 JUN - 22 JUL Kreeft 21 juni - 23 juli Ik voel

Emoties ; Afkomst; Gevoel voor traditie
Huis; Familie; Veiligheid en geborgenheid
Onberedeneerbare stemmingen die uit het diepst van ons innerlijk (de psyche); omhoogkomen.
Heerser Maan:
- zegt iets over je onbewuste gedrag en het gedrag dat je inschakelt om weer op poten te
komen
- hoe je met gevoelens en met intimiteiten omgaat
- in hoeverre je "echt bij jezelf" kunt zijn
- hoe je omgaat met voeding en je eigen huis hoe je omgaat met veiligheid en emotionele
geborgenheid
- hoe je je ervaringen verwerkt en verteert en omgaat met je verleden
Cancer is the Cardinal-Water sign. Cancerians get things done through the power of their emotional commitment. Not only do they aggressively work to accomplish the goals inspired by their own feelings, they also know how to appeal to the emotions of others. By making other people feel like family members, Cancerians effectively inspire others to help get projects accomplished. They have to learn how to reach an emotional balance since they tend to be overly sensitive and moody. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22)) ORDER BY lastname, firstname, personID;  
144 23  Leeuw  Personen, geboren in het astrologische teken leeuw (Leo): 23 juli -23 augustus
Individuals born in astrological sign leo: 23 JUL - 23 AUG Leeuw 23 juli - 23 augustus Ik wil Zelfexpressie en eigenwaarde
De presentatie van het ego
De scheppingsdrang
Heerser Zon;
- zegt iets over je bewuste gedrag
- geeft de weg aan die 'n mens het beste kan gaan om zich in overeenstemming met z’n diepste
ware zijn te ontplooien
- hoe je omgaat met creativiteit, romantiek, kinderen en in hoeverre je "het kind in jezelf" een
plaats durft te geven.
- of je vertrouwen hebt in je intuïtie en je hart, jezelf durft te zijn
Leo is the Fixed-Fire sign. Leos stubbornly cling to their pride. They resent the indignity of altering their opinions or behavior in front of or at the request of others. Their stubborn nature makes it hard for them to accept that there is no virtue in giving what they want to give rather than what may really be wanted or needed, and no reward in misguided loyalty to those who are not worthy of it. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID;  
145 271  Length of marriage, ordered by alphabet  Lengte van een huwelijk, gesorteerd op alfabet  select
CONCAT('<a href = \"familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\" target=\"_blank\" class=\"fam\">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by P1.lastname, YearsMarried desc, F1.marrdatetr desc
;  
146 272  Length of marriage, ordered by length of marriage  Aantal jaren getrouwd, gesorteerd op aantal jaren getrouwd  select
CONCAT('<a href = \"familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\" target=\"_blank\" class=\"fam\">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by YearsMarried desc, P1.lastname, F1.marrdatetr desc
;  
147 141  Levende Boekholt's  Je moet ingelogd zijn om hier iets nuttigs te zien  SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like "boekh%") ORDER BY firstname 
148 142  Levende personen  Je moet ingelogd zijn om hier iets nuttigs te zien  SELECT tng_people.living, firstname, lastname, birthdate, deathdate, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1) ORDER BY firstname 
149 140  Levende Savenije's  Je moet ingelogd zijn om hier iets te zien  SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like "sav%n%") ORDER BY firstname 
150 226  List eventypes  List even types with eventypeID  SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC 
151 233  List of men who were eligable to fight in the papal wars  Lijst van mensen die eventueel als Zouaaf tegen Garibaldi gevochten zouden kunnen hebben  SELECT p.personID, p.lastname, p.firstname, p.birthplace, et.description AS Conflict, 1861 - YEAR( p.birthdatetr ) AS age_at_beginning_of_papal_wars, e.eventdate AS Event_Date, e.eventplace AS Event, p.birthdate, p.deathdate, p.living FROM tng_people AS p LEFT OUTER JOIN tng_events AS e ON ( p.personID = e.persfamID AND p.gedcom = e.gedcom ) LEFT OUTER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID WHERE birthdatetr <>0000 -00 -00 AND ( 1861 - YEAR( birthdatetr ) >=18 ) AND ( 1861 - YEAR( birthdatetr ) <=40 ) AND YEAR( deathdatetr ) >1861 AND sex = "M" AND ( birthdate NOT LIKE "Aft%" ) AND Length(p.firstname) >10 and (p.firstname like "%es %" or p.firstname like "%us %" or p.firstname like "%as%") AND (p.birthplace like "%Groningen" or birthplace like "%Drenthe")
AND
( ( ( et.tag = "EVEN" AND description LIKE "Mili%" ) OR ( et.tag = "EVEN" AND et.description = "Civil War" ) OR ( et.tag = "EVEN" AND et.description LIKE "Revolutionary%" ) OR ( et.tag = "EVEN" AND et.description LIKE "WWI%" ) OR ( et.tag = "EVEN" AND et.description LIKE "Vietnam%" ) OR ( et.tag = "EVEN" AND et.description LIKE "Korean%" ) OR ( et.tag = "EVEN" AND et.description LIKE "War of 1812%" ) ) OR et.tag IS NULL ) ORDER BY p.lastname, p.firstname, p.personID, age_at_beginning_of_papal_wars 
152 24  Maagd  Personen, geboren in het astrologische teken (Virgo): 24 augustus-23 september
Individuals born in astrological sign virgo: 24 AUG - 23 SEP Maagd 23 augustus - 23 september Ik onderzoek
Werk. Een taak hebben
Dienstbaar en praktisch zijn
Kritisch vermogen
Gezondheid en het opbouwen van levensgewoonten
Heerser Mercurius ;
- hoe je met je werk omgaat
- hoe je analyseert en je bezig houdt met het detail
- in hoeverre je dienstbaar kunt zijn en kunt geven aan een doel buiten jezelf
Virgo is the Mutable-Earth sign. Virgos adapt to different people and situations by finding ways to make themselves useful. To hide their vulnerability, they focus attention on what they're doing rather than who they are. To deflect attention away from themselves, Virgos will also focus on other people by praising their talents and virtues, or just as likely, by listing their faulty behavior or personal defects. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID;  
153 291  Maand en dag waarop de meeste huwlijken plaats vinden  Month and day on which most marriages take place  SELECT month(marrdatetr) as month, day(marrdatetr) as day, count(*) as cnt
FROM tng_families
WHERE year(marrdatetr) != 0
and month(marrdatetr) != 0
and day(marrdatetr) != 0
GROUP BY day(marrdatetr),month(marrdatetr)
HAVING cnt > 0
ORDER BY cnt desc, month, day 
154 176  Media not associated with a tree  Media die niet aan een boom vastzit  SELECT mediaID AS MediaNr, description, mediatypeID AS Media_type FROM tng_media WHERE (gedcom="" or gedcom is null) ORDER BY description; 
155 174  Media overview by media type  Media overzicht per media type.   SELECT mediatypeID AS MediaType, mediaID AS MediaNr, description, gedcom FROM tng_media ORDER BY mediatypeID, description;  
156 173  Media statistics  Media statistieken  SELECT mediatypeID AS Media_Type, COUNT(*) AS Number FROM tng_media GROUP BY mediatypeID
UNION
SELECT "Total" AS Media_Type, COUNT(*) AS Number FROM tng_media; 
157 182  Media which are *not* set as "default photo"  Media die NIET als standaard foto zijn aangevinked  SELECT description, mediatypeID AS Media_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE defphoto<>1
ORDER BY description;  
158 175  Media which are always visible  Media having the "always on" tag activated

Media die als "Altijd zichtbaar" zijn gemarkeerd 
SELECT description, mediatypeID AS Mediia_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE alwayson<>0
ORDER BY description;  
159 181  Media which are set as "default photo"  Media die als standaard foto zijn aangevinked  SELECT description, mediatypeID AS Media_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE defphoto=1
ORDER BY description;  
160 180  Media with associated people, *with* having media linked to an event  Media with associated people, *with* having media linked to an event

Media met eraan gelinkte mensen MET media gelinked aan een gebeurtenis
 
SELECT description, mediatypeID AS Media_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE eventID<>""
ORDER BY description;  
161 179  Media with associated people, *without* having media linked to an event  Media with associated people, *without* having media linked to an event

Media met de eraan gelinked mensen zonder dat de media aan een gebeurtenis gelinked zijn.  
SELECT description, mediatypeID AS Media_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE eventID=""
ORDER BY description;  
162 178  Media with coordinates  Media met coordinaten.   SELECT mediaID AS MediaNr, description, mediatypeID AS Media_Type, longitude, latitude, gedcom FROM tng_media WHERE longitude<>"" AND latitude<>"" AND NOT ISNULL(longitude) AND NOT ISNULL(latitude) ORDER BY description;  
163 177  Media without coordinates  Media zonder coordinaten  SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="" OR latitude="" ORDER BY description;  
164 279  Mensen zonder geboorteplaats maar met geboortedatum  People without birthplace but with birthdate  SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby
FROM `tng_people`
WHERE birthplace = ""
and birthdate != "" and birthdate != "y" and changedby ="Michiel"
ORDER BY lastname 
165 286  Notities die niet meer gelinked zijn  Notes which aren't linked anymore.   SELECT ID, note, noteid, gedcom
FROM `tng_xnotes` WHERE noteid = "" ORDER BY `tng_xnotes`.`ID` ASC; 
166 269  Number of children a man fathered  Het aantal kinderen die een man voorbracht  SELECT f.gedcom, count(c.personid) as Children,
concat('<a href="descendtext.php?personID=',h.personid,'&tree=savenije&display=block&generations=2">',concat(h.firstname,' ',h.lastname),'</a>') as Husband
FROM tng_families as f
left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid
join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid
WHERE f.gedcom = "savenije"
AND h.firstname NOT LIKE '(null%'
group by c.gedcom,h.personid
order by Children desc 
167 228  Number of people originating from first level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus dorp  SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",3))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY city_or_village order by Number desc; 
168 227  Number of people originating from second level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus gemeente   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",2))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY city_or_village order by Number desc; 
169 189  Number of people originating from third level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus provincie of land.   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY provincie_or_country order by Number desc; 
170 207  Number of people with the same last and first name ordered alphabetically  Aantal mensen die dezelfde voor en last_name hebben, alphabetisch gerangschikt  SELECT lastname, firstname, COUNT(CONCAT(lastname, firstname)) AS Number FROM tng_people GROUP BY lastname, firstname HAVING COUNT(CONCAT(lastname, firstname))>1 ORDER BY lastname, firstname;  
171 162  Orphaned families  Families with no husband and no wife
Gezinnen met geen vader en geen mother 
SELECT familyid, husband AS husbandPersonID, wife AS WifePersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE husband="" AND wife="" ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED);  
172 183  People *with* associated media, but *without* default photo  Mensen MET plaatjes, maar zonder standaard plaatje  SELECT p.personID, p.lastname, p.firstname, p.living, p.gedcom, description, mediatypeID AS Media_type FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE defphoto<>1
ORDER BY lastname, firstname, birthdatetr;  
173 157  People born after they died  Personen geboren nadat ze overleden zijn.   SELECT personID, firstname, lastname, birthdate, birthdatetr, birthplace, deathdate, deathdatetr, deathplace, YEAR( deathdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby
FROM tng_people
WHERE (
(
`birthdatetr`
) - ( `deathdatetr` ) >0
)
AND `birthdatetr` <>0000 -00 -00
AND `deathdatetr` <>0000 -00 -00
AND deathdate != "y"
AND deathdate != "0"
AND `living` = "0"
AND deathdate != "n"
AND ( deathdatetr ) - ( birthdatetr ) !=0 
174 170  People born into more families  Mensen die in meerdere gezinnen zijn geboren  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, COUNT(*) AS number_of_families, p.gedcom, p.changedby FROM tng_children AS ch
LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom)
GROUP BY personID HAVING COUNT(*)>1 ORDER BY lastname, firstname;  
175 237  People buried before death  Mensen die begraven zijn voordat ze zijn gestorven  SELECT personID, firstname, lastname, deathdate, deathdatetr, burialdate, burialdatetr, YEAR( burialdatetr ) - YEAR( deathdatetr ) AS difference, changedby, gedcom
FROM tng_people
WHERE (
burialdatetr - deathdatetr <0
)
AND (
`burialdatetr` !=0000 -00 -00
OR YEAR( burialdatetr ) !=0000
)
AND birthdate != ""
AND burialdate != ""
AND `living` = "0"
AND burialdate != "n"
AND burialdatetr - deathdatetr !=0 
176 167  People ordered with the age of their parents  People ordered with the age of their parents ordered according to the age of the father

Mensen geordend MET de leeftijd van hun ouders, gerangschikked volgens de leeftijd van de vader 
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.changedby, p.gedcom
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr, p.changedby 
177 264  People sorted on ID  Mensen gesorteerd op het ID  SELECT ID, personID, firstname, lastname, birthdate, birthplace, changedby
FROM tng_people
ORDER BY CAST( SUBSTRING( personID, 2 ) AS UNSIGNED )  
178 263  People with a burial place but no headstone  Mensen met een begraafplaats maar geen grafsteen  SELECT concat('<a href="getperson.php?personID=',p.personid,'&tree=',
p.gedcom,'">', p.firstname,' ',p.lastname) AS Name, p.burialplace
FROM tng_people p
WHERE p.burialplace <> ''
AND NOT EXISTS
(
SELECT
ml.personID
FROM
tng_medialinks ml
WHERE
p.personID = ml.personID AND
p.gedcom = ml.gedcom AND
ml.eventID = 'BURI'
)
ORDER BY p.burialplace 
179 166  People with a different surname as their father  People with a different surname as their father (born after 1811)

Mensen met een andere achternaam als hun vader (geboren na 1811) 
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS FatherNr, father.lastname AS Fatherlast_name, mother.personID AS MotherNr, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname<>father.lastname AND YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr;  
180 304  People with aproximate birthdates in the provinces of Groningen and Drenthe<1810  Mensen met een "ongeveer" geboortedag in Groningen en Drenthe voor 1810 (via www.allegroningers.nl en www.drenlias.nl zijn die wel in te vullen)

De geschatte datums heb ik eruit gehaald, omdat die kennelijk niet gevonden konden worden.  
SELECT ID, personID, lastname, firstname, birthdate, birthplace, altbirthdate, deathdate, deathplace, burialdate, living, gedcom, changedate, changedby
FROM tng_people
WHERE (
(
UCASE( birthdate ) LIKE "Abt%"
OR UCASE( birthdate ) LIKE "Cal%"
OR (
LENGTH( birthdate ) = "4"
AND altbirthdate = ""
)
)
AND (
(
birthplace LIKE "%Groningen"
AND (
YEAR( birthdatetr ) < "1810"
AND YEAR( birthdatetr ) > "1650"
)
)
OR (
birthplace LIKE "%Drenthe"
AND YEAR( birthdatetr ) < "1810"
AND YEAR( birthdatetr ) > "1650"
)
)
)
ORDER BY ID, changedate, lastname, firstname, personID 
181 235  People with aproximate birthdates in the provinces of Groningen and Drenthe>1810  Mensen met een "ongeveer" geboortedag in Groningen en Drenthe tussen 1810 en 100 jaar geleden (via www.allegroningers.nl en www.drenlias.nl zijn die wel in te vullen)

De geschatte datums heb ik eruit gehaald, omdat die kennelijk niet gevonden konden worden.  
SELECT ID, personID, lastname, firstname, birthdate, birthplace, altbirthdate, deathdate, deathplace, burialdate, living, gedcom, changedate, changedby
FROM tng_people
WHERE (
(
UCASE( birthdate ) LIKE "Abt%"
OR UCASE( birthdate ) LIKE "Cal%"
OR (
LENGTH( birthdate ) = "4"
AND altbirthdate = ""
)
)
AND (
(
birthplace LIKE "%Groningen"
AND (
YEAR( birthdatetr ) < ( year( curdate( ) ) -100 )
AND YEAR( birthdatetr ) > "1809"
)
)
OR (
birthplace LIKE "%Drenthe"
AND YEAR( birthdatetr ) < ( year( curdate( ) ) -100 )
AND YEAR( birthdatetr ) > "1809"
)
)
)
ORDER BY ID, changedate, lastname, firstname, personID 
182 206  People with non-alphabetic characters in their name  Mensen met niet alphabetische karakters in hun naam.   SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((lastname REGEXP "[^[:alpha:][:space:]-]")>0) OR ((firstname REGEXP "[^[:alpha:][:space:]-]")>0)
ORDER BY lastname, firstname;  
183 164  People with the same surname as their mother  People with a different surname as their father but the same as their mother (born after 1811, due to the Dutch system, before 1811 people used patronymics)
Mensen met een andere last_name dan hun vader geboren na 1811 (voor 1811 gebruikte men patroniemen)maar dezelfde als hun mother 
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS FatherNr, father.lastname AS Fatherlast_name, mother.personID AS MotherNr, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE (p.lastname=mother.lastname AND YEAR(p.birthdatetr)>"1811") AND father.lastname <> "" AND father.lastname <> mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr;  
184 159  People without a default image but with a photo attached  Mensen zonder een standaard klikplaatje maar waar wel een foto aan gelinked is.  SELECT p1.personID, p1.lastname, p1.firstname, p1.living, p1.gedcom, m1.description, m1.mediatypeID
FROM tng_media m1
LEFT OUTER JOIN tng_medialinks m2 ON m1.gedcom = m2.gedcom
AND m1.mediaID = m2.mediaID
LEFT OUTER JOIN tng_people p1 ON m2.gedcom = p1.gedcom
AND m2.personID = p1.personID
LEFT OUTER JOIN (

SELECT gedcom, personID, defphoto
FROM tng_medialinks
WHERE defphoto = "1"
)m3 ON p1.gedcom = m3.gedcom
AND p1.personID = m3.personID
WHERE m2.medialinkID IS NOT NULL
AND m1.mediatypeID != "documents"
AND m1.mediatypeID != "histories"
AND m1.mediatypeID != "headstones"
AND p1.personID IS NOT NULL
AND m3.personID IS NULL
ORDER BY p1.lastname, p1.firstname, p1.birthdatetr
 
185 184  People without any dates  Mensen zonder enige datums  SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE
((birthdate is NULL) OR (birthdate="")) AND
(birthdatetr="0000-00-00") AND
((altbirthdate is NULL) OR (altbirthdate="")) AND
(altbirthdatetr="0000-00-00") AND
((deathdate is NULL) OR (deathdate="")) AND
(deathdatetr="0000-00-00") AND
((burialdate is NULL) OR (burialdate="")) AND
(burialdatetr="0000-00-00")
ORDER BY lastname, firstname;  
186 275  People without parents born between 1800 and 1911  Mensen die tussen 1800 en 1911 zijn geboren in Groningen en Drenthe zonder ouders  SELECT personID, firstname AS first_name, CONCAT( lnprefix, " ", lastname ) AS last_name, birthdate AS birth_date, birthdatetr, birthplace AS place_of_birth, changedate AS Change_date, gedcom, changedby
FROM tng_people
WHERE famc = ""
AND birthdatetr != "0000-00-00"
AND (
birthdatetr >= "1800-00-00"
AND birthdatetr <= "1911-00-00"
)
AND (
birthplace LIKE "%Groningen"
OR birthplace LIKE "%Drenthe"
)
ORDER BY birthdatetr ASC 
187 295  Personen met een "bijna" geboortedatum in Overijssel    SELECT ID,personID, lastname, firstname, birthdate,birthplace, altbirthdate, deathdate, deathplace, burialdate, living, gedcom
FROM tng_people
WHERE (
(
UCASE( birthdate ) LIKE "Abt%"
OR UCASE( birthdate ) LIKE "Cal%"
OR (
LENGTH( birthdate ) = "4"
AND altbirthdate = ""
)
)
AND (
(
birthplace LIKE "%Overijssel"
AND
(YEAR( birthdatetr ) < "1911" AND YEAR( birthdatetr ) > "1700")
)

)
)
ORDER BY ID, lastname, firstname, personID 
188 197  Persons whose last name is the same as the last name of their mother  Personen met dezelfde achternaam als hun moeder  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS Father_Nr, father.lastname AS Father_lastname, mother.personID AS Mother_Nr, mother.lastname AS Mother_lastname FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname=mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr;  
189 199  persons whose last names are different from last name of father *and* last name  Mensen die een verschillende achternaam hebben als hun vader EN moeder  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Last_name_father, mother.lastname AS Last_name_mother FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname<>father.lastname AND p.lastname<>mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr;  
190 38  Photos changed within the last 90 days  Photos changed within the last 90 days (listing *with* linked individuals)   SELECT description, m.notes, m.changedate, p.personID, p.gedcom, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=ml.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE mediatypeID<>"headstones" AND DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=m.changedate
ORDER BY m.changedate DESC;  
191 296  plaatsen in Nederland geordend volgens plaatsen met coordinaten en beschrijving  Places in the Netherlands, ordered according to placenames with coordinates and description  SELECT place, longitude, latitude, notes, ID
FROM tng_places
WHERE place LIKE "%, Groningen"
OR place LIKE "%, Friesland"
OR place LIKE "%, Drenthe"
OR place LIKE "%, Overijssel"
OR place LIKE "%, Gelderland"
OR place LIKE "%, Utrecht"
OR place LIKE "% Holland"
OR place LIKE "%, Zeeland"
OR place LIKE "%, Noord-Brabant"
OR place LIKE "%, Limburg"
OR place LIKE "%,Flevoland"
OR place LIKE "% Polder"
ORDER BY place ASC  
192 289  Plaatsnamen in Groningen en Drenthe  Places in Groningen and Drenthe  SELECT place, longitude, latitude, notes
FROM `tng_places`
WHERE place LIKE "%Drenthe"
OR place LIKE "%, Groningen"
ORDER BY `tng_places`.`place` ASC 
193 300  Placenames in the Netherlands without coordinates and description  Plaatsnamen in Nederland zonder coordinaten en beschrijving  SELECT place
FROM tng_places
WHERE (
place LIKE "%, Noord-Brabant"
OR place LIKE "%, Zeeland"
OR place LIKE "%, Limburg"
OR place LIKE "%, Noord-Holland"
OR place LIKE "%, Zuid-Holland"
OR place LIKE "%, Utrecht"
OR place LIKE "%, Gelderland"
OR place LIKE "%, Overijssel"
OR place LIKE "%, Drenthe"
OR place LIKE "%, Friesland"
OR place LIKE "%, Groningen"
)
ORDER BY place; 
194 203  Placenames in the Netherlands without maps  Plaatsnamen in Nederland waaraan nog geen oude kaart is verbonden  SELECT place, pl.gedcom
FROM tng_places AS pl
LEFT JOIN tng_medialinks AS p ON ( p.personID = pl.place
AND p.gedcom = pl.gedcom )
WHERE ISNULL( personID )
AND (
personID LIKE "%, Noord-Brabant"
OR place LIKE "%, Zeeland"
OR place LIKE "%, Limburg"
OR place LIKE "%, Noord-Holland"
OR place LIKE "%, Zuid-Holland"
OR place LIKE "%, Utrecht"
OR place LIKE "%, Gelderland"
OR place LIKE "%, Overijssel"
OR place LIKE "%, Drenthe"
OR place LIKE "%, Friesland"
OR place LIKE "%, Groningen"
); 
195 131  Places ordered by the last entered  Plaatsnamen georderend volgens de laatst toegevoegde  SELECT place,longitude,latitude, notes, ID FROM tng_places
ORDER BY ID DESC; 
196 257  Places sorted from biggest entity to smallest    SELECT place
FROM tng_places
ORDER BY
CASE WHEN LOCATE( ',', place ) =0
THEN place
ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) )
END ,
CASE WHEN LOCATE( ',', place ) =0
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
End 
197 154  Places with an empty description but with coordinates  Plaatsnamen zonder een beschrijving, maar met coordinaten  SELECT place, longitude, latitude, zoom, placelevel, notes
FROM `tng_places`
WHERE (
notes = ""
OR notes is NULL
)
AND (
Longitude <> ""
AND latitude <> ""
198 126  Places without coordinates    SELECT place,longitude,latitude, notes FROM tng_places WHERE longitude = "" OR latitude="" OR longitude is null OR latitude is null ORDER BY place; 
199 301  Places without events  Plaatsen zonder gebeurtenissen (in principe plaatsnamen die niet in gebruik zijn)  SELECT id, gedcom, concat('<a
href=admin_places.php?tree=',gedcom,'&searchstring=',REPLACE(place,'
','%20'),'&exactmatch=yes&findplace=1&newsearch=1>',place,'</a>') AS place,
longitude, latitude, notes
FROM tng_places
WHERE place
IN
( SELECT pl.place FROM tng_places AS pl
LEFT JOIN
(
SELECT birthplace AS place FROM tng_people
UNION
SELECT altbirthplace FROM tng_people
UNION
SELECT baptplace FROM tng_people
UNION
SELECT deathplace FROM tng_people
UNION
SELECT burialplace FROM tng_people
UNION
SELECT marrplace FROM tng_families
UNION
SELECT divplace FROM tng_families
UNION
SELECT eventplace FROM tng_events
UNION
SELECT place FROM tng_cemeteries
UNION
SELECT personID FROM tng_medialinks
WHERE linktype = "L"
) AS p
USING ( place )
WHERE isnull( p.place ) ) 
200 298  Possible duplicates   Looks for people with the same names and birth years
Mogelijke dubbelen, zoekt naar mensen met dezelfde namen en hetzelfde geboortejaar. 
SELECT p1.personID, p1.lnprefix, p1.lastname, p1.firstname, p1.birthdate, p1.birthplace, p1.altbirthdate, p1.deathdate, p1.gedcom
FROM tng_people p1
INNER JOIN (

SELECT p2.lastname, p2.firstname, p2.gedcom, EXTRACT( YEAR
FROM birthdatetr ) AS BirthYear, COUNT( * ) AS Number
FROM tng_people p2
WHERE p2.gedcom = 'savenije'
AND p2.birthdatetr <> '0000-00-00'
AND p2.firstname <> 'Living'
AND NOT (
p2.firstname = 'NN'
OR p2.lastname = 'NN'
)
GROUP BY p2.lastname, p2.firstname, BirthYear
HAVING Number >1
)p3 ON p1.gedcom = p3.gedcom
AND p1.lastname = p3.lastname
AND SUBSTRING_INDEX( TRIM( p1.firstname ) , ' ', 1 ) = SUBSTRING_INDEX( TRIM( p3.firstname ) , ' ', 1 )
AND EXTRACT( YEAR
FROM p1.birthdatetr ) = p3.BirthYear
ORDER BY p1.lastname, p1.firstname, p1.birthdatetr 
201 303  Possible duplicates  Looks for couples with the same names
Mogelijke dubbelen, zoekt naar echtparen met dezelfde namen.  
select f1.familyID, h1.personID as h1id,h1.firstname as h1fn,h1.lastname as h1ln,h1.birthdate as h1bd,' married to ' as txt1,w1.personID as w1id,w1.firstname as w1fn,w1.lastname as w1ln,w1.birthdate as w1bd,f2.familyID as f2id, h2.personID as h2id,h2.firstname as h2fn,h2.lastname as h2ln,h2.birthdate as h2bd,' married to ' as txt2,w2.personid as w2id,w2.firstname as w2fn,w2.lastname as w2ln,w2.birthdate as w2bd


from tng_families f1
inner join tng_people as w1 on f1.wife = w1.personID and f1.gedcom = w1.gedcom
inner join tng_people as h1 on f1.husband = h1.personID and f1.gedcom = h1.gedcom


inner join tng_people as h2 on h2.lastname = h1.lastname and substr(h1.firstname,1,4) = substr(h2.firstname,1,4) and f1.gedcom = h2.gedcom
inner join tng_people as w2 on w1.lastname = w2.lastname and substr(w1.firstname,1,4) = substr(w2.firstname,1,4) and f1.gedcom = w2.gedcom
inner join tng_families as f2 on h2.personId = f2.husband and w2.personID = f2.wife and f2.gedcom=f1.gedcom


where w2.personID <> w1.personID
and h2.personID <> h1.personID
and h1.firstname <> 'Unknown'
and h1.firstname <> 'Living'
and w1.firstname <> 'Unknown'
and w1.firstname <> 'Living'
and h1.lastname <> 'Unknown'
and w1.lastname <> 'Unknown'
 
202 19  Ram  Personen, geboren in het astrologische teken Ram (Aries): 21 maart -20 April
Ram 21 maart - 21 april Ik ben

Het ik ben
Het pure begin.
De impulsieve aktie
Heerser Mars;
- zegt iets over de manier waarop je je energie wilt besteden
- hoe je voor jezelf opkomt
- hoe je akties onderneemt en jezelf onderscheidt van anderen
Aries is the Cardinal-Fire sign. Aries people need to keep physically busy. They accomplish many things simply because of their restless energy. They need to learn how to make constructive use of their energetic efforts. The typical Aries urge is to take on more projects than can be done reasonably well. Though others may find it difficult to physically keep pace, they are attracted to the animation and spirit of Aries personalities. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID;  
203 273  Report List and code, lijst met alle rapporten en code  If the tree is mentioned as savenije please replace with your won tree. Als de naam van de tree=savenije vervang die door uw eigen stamboom naam  SELECT reportID, reportname, reportdesc, REPLACE(REPLACE(sqlselect, '<', X'266C743B'), '>', X'2667743B') AS sqlselect, active FROM tng_reports WHERE sqlselect<>"" AND active="1" ORDER by reportname;  
204 105  Same sex marriages    SELECT f.familyID, h.personID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, h.birthdate as birtdate1, h.sex AS gender1, w.personID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, w.birthdate as birtdate2, w.sex AS gender2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE h.sex=w.sex ORDER BY familyID;  
205 26  Schorpioen  Personen, geboren in het astrologische teken Schorpioen (Skorpius): 24.10.-22.11.
Individuals born in astrological sign scorpio: 24 OCT - 22 NOVSchorpioen 23 oktober - 22 november Ik geniet of ik vernietig

Transformatie- en verwerkingsprocessen.
Psychologie Machtsbehoefte. Wilskracht en sexualiteit
Het verborgene en alles wat ooit verdrongen werd
Heerser Pluto;
- geeft aan hoe je met diepe innerlijke veranderingen omgaat
- hoe de sexualiteit wordt beleefd als een totale overgave aan de ander.
- geeft aan waar je je "donkere kant" , om met Jung te spreken: je schaduw ontmoet.
Scorpio is the Fixed-Water sign. Scorpios stubbornly cling to emotional attachments. They rarely forget or forgive emotional rejection. They have to learn that jealousy and possessiveness are self-defeating. Rechanneling negative feelings and experiences into constructive activities benefits others as well as themselves. No other sign has the emotional strength of Scorpio. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22)) ORDER BY lastname, firstname, personID; 
206 160  Show private notes  Toon privé notities  SELECT personID, lastname, firstname, birthdate, deathdate, living, note,
p.gedcom FROM tng_people AS p
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND
p.gedcom=nl.gedcom)
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE nl.secret<>0
ORDER BY lastname, firstname, birthdatetr; 
207 302  Siblings marrying siblings  2 broers of zusters van een gezin trouwen met 2 zusters of broers in een ander. 2 brothers or sisters in one family marrying 2 sisters or brothers in another.   SELECT
p1.gedcom
,p1.personID
,CONCAT(p1.firstname,' ',p1.lastname) AS PersonName
,f1.marrdate
,p2.personID as SpouseID
,CONCAT(p2.firstname,' ',p2.lastname) AS SpouseName


FROM
tng_families f1
INNER JOIN
tng_people p1
ON
f1.gedcom = p1.gedcom
AND
f1.husband = p1.personID
INNER JOIN
tng_people p2
ON
f1.gedcom = p2.gedcom
AND
f1.wife = p2.personID
WHERE
p1.famc <> ""
AND
p2.famc <> ""
AND
p1.famc <> p2.famc
AND
(p1.gedcom, CONCAT(CASE WHEN p1.famc < p2.famc THEN p1.famc ELSE p2.famc END, CASE WHEN p1.famc > p2.famc THEN p1.famc ELSE p2.famc END)) IN
(SELECT
q2.gedcom
,CONCAT(q2.family1, q2.family2) AS Parents
FROM
(SELECT
f1.gedcom
,CASE WHEN p1.famc < p2.famc THEN p1.famc ELSE p2.famc END AS Family1
,CASE WHEN p1.famc > p2.famc THEN p1.famc ELSE p2.famc END AS Family2
, COUNT(*) AS Frequency
FROM
tng_families f1
INNER JOIN
tng_people p1
ON
f1.gedcom = p1.gedcom
AND
f1.husband = p1.personID
INNER JOIN
tng_people p2
ON
f1.gedcom = p2.gedcom
AND
f1.wife = p2.personID
WHERE
p1.famc <> ""
AND
p2.famc <> ""
GROUP BY
p1.famc
,p2.famc
HAVING
Frequency > 1) AS q2)
ORDER BY
p1.famc
,p1.lastname
,p1.firstname 
208 215  sources with citation frequency and number of cited persons, ordered by citation  Bronnen met frequenties van citaten en aantal geciteerde personen, gerangschikt naar frequentie van citaten.  SELECT s.sourceID AS Source_nr, s.title AS Long_title, s.shorttitle AS Short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS Comments_, COUNT(*) AS Number_of_citations, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY Number_of_citations DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED);  
209 216  sources with citation frequency and number of cited persons, ordered by number o  Bronnen met frequentie van citaten and aantal geciteerde personen, gerangschikt naar geciteerde personen.  SELECT s.sourceID AS Source_nr, s.title AS Long_title, s.shorttitle AS Short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS Comments_, COUNT(*) AS Number_of_citations, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY Number_of_people DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED);  
210 47  sources with citation frequency, ordered by frequency  sources with citation frequency, ordered by frequency   SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Remarks, COUNT(*) AS Frequency FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY Frequency  
211 46  sources with citation frequency, ordered by sources  sources with citation frequency, ordered by sources   SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Comments, COUNT(*) AS Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY s.sourceID;  
212 214  Sources with frequency and persons, ordered by sources   Bronnen met frequentie en personen, gerangschikt naar bronnen  SELECT s.sourceID AS QuellenNr, s.title AS long_title, s.shorttitle AS short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS comments_, COUNT(*) AS number_of_times_quoted, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
INNER JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED);  
213 221  sources with notes, including note contents   Bronnen met notities, inclusief de inhoud  SELECT sourceID, title AS Long_title, xn.note, s.gedcom FROM tng_notelinks AS nl
LEFT JOIN tng_sources AS s ON (nl.persfamID=s.sourceID AND nl.gedcom=s.gedcom)
LEFT JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE (NOT ISNULL(sourceID)) AND nl.secret=0 ORDER BY title;  
214 218  sources without any citations  Bronnen zonder enige citaten  SELECT s.sourceID AS knowledge_bank, title AS Title_, s.gedcom FROM tng_sources AS s
LEFT OUTER JOIN tng_citations AS c ON (s.sourceID=c.sourceID and s.gedcom=c.gedcom)
WHERE citationID IS NULL;  
215 48  sources: citation texts - with frequency of occurence  sources: citation texts - with frequency of occurence ordered by description  SELECT description, COUNT(*) AS Number FROM tng_citations GROUP BY description ORDER BY description;  
216 213  Sources: citations associated with families, just the link  Bronnen: citaten geaccossieert met gezinnen, alleen de link  SELECT f.familyID, h.personID, h.lastname AS Last_name1, h.firstname AS First_name1, h.living AS lving1, w.PersonID, w.lastname AS Last_name2, w.firstname AS First_name2, w.living AS lving2, c.sourceID, c.eventID, c.description, f.gedcom FROM tng_citations AS c
LEFT JOIN tng_families AS f ON (c.persfamID=f.familyID AND c.gedcom=f.gedcom)
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)
WHERE familyID<>'' ORDER BY h.lastname, h.firstname, h.personID, c.description;  
217 220  sources: citations with associated individuals, ordered by citation text  Bronnen: citaten met geaccossieerde personen, gerangschikt naar geciteerde tekst.   SELECT c.description, c.sourceID, c.eventID, c.citetext, c.page, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>"" ORDER BY c.description, c.citetext, c.page, c.sourceID;  
218 219  sources: citations with associated individuals, ordered by individual's name  Bronnen: citaten met geaccossieerde personen, gerangschikt naar iemand's naam  SELECT p.personID, p.lastname, p.firstname, p.living, c.eventID, c.sourceID, c.description, c.citetext, c.page, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>'' ORDER BY p.lastname, p.firstname, p.personID, c.description;  
219 169  Statistic of people becoming parents  Frequency distribution of age where males (M) become father and females (F) become mother (for all children)

Verdeling van leeftijd waarop mensen ouder worden 
SELECT YEAR(p.birthdatetr)-YEAR(father.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'M') AS Graph FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom)
WHERE p.birthdatetr<>"0000-00-00" AND father.birthdatetr<>"0000-00-00" AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)<60 GROUP BY parents_age
UNION
SELECT YEAR(p.birthdatetr)-YEAR(mother.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'F') AS Graph FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.birthdatetr<>"0000-00-00" AND mother.birthdatetr<>"0000-00-00" AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)<60 GROUP BY parents_age
ORDER BY parents_age;  
220 192  Statistics of places where people were baptized  Statistieken waar mensen gedoopt zijn.   SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(altbirthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthplace <> "" group BY gemeente_or_state order by Number desc; 
221 28  Steenbok  Personen, geboren in het astrologische teken Steenbok (Capricornus): 22.12.-20.01.
Individuals born in astrological sign capricornus: 22 DEC - 20 JAN

Steenbok 22 december - 21 januari Ik streef mijn doel na

Maatschappelijke positie. Je doelen
Het leerproces van de pijn. Wetten
Plichten en verantwoordelijkheden
Heerser Saturnus
- geeft aan hoe we door noodzakelijke beperkingen onze eigen vorm moeten zien te vinden,
om daarmee te kunnen funktioneren in de maatschappij
- hoe je met verantwoordelijkheden, plichten en allerlei wetten omgaat
- welke doelen je jezelf stelt
Capricorn is the Cardinal-Earth sign. Capricorns are natural goal setters. They willingly handle many tasks if it helps them get what they want. Many things get accomplished simply because they happen to be part of Capricorn's overall efforts to reach higher goals. They need definitive guidelines. Rules and regulations provide structure they need for establishing the pattern of their own actions. They must learn however, that the end never justifies the means.
 

SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID 
222 20  Stier  Personen, geboren in het astrologische teken Stier (Taurus): 21 april-20 mei
Individuals born in astrological sign taurus: 21 APR - 20 MAY Stier 21 april - 21 mei Ik heb Materieel en geestelijk bezit, principes en ideeën
Zekerheden en dingen die van waarde zijn voor iemand.
Je motivatie en vaardigheden
Heerser Venus
- zegt hoe je omgaat met het aardse, met je lichaam en hoe je "in je lijf zit"
- over je gevoel voor harmonie en schoonheid, dingen waarvan je geniet
- hoe je motivatie is om iets te doen, en op welke manier je met je vaardigheden omgaat
Taurus is the Fixed-Earth sign. Taureans stubbornly cling to their own ideas and habits, and may fail to take advantage of new ideas or situations simply because they cannot see their practical use. They have to understand that while tenacity and a stable temperament yield rewards in many endeavors, tolerance and flexibility are the best assets when it comes to personal relationships. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID;  
223 299  Surname list with frequency  Achternamen lijst met frequentie  SELECT

UPPER(lastname) AS Surname,

COUNT(*) AS Individuals,

SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) as Male,

SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) as Female,

IFNULL(MIN(CASE birthdatetr WHEN '0000-00-00' THEN NULL

ELSE YEAR(birthdatetr)

END),'') as Earliest_Birth,

IFNULL(MAX(CASE birthdatetr WHEN '0000-00-00' THEN NULL

ELSE YEAR(birthdatetr)

END),'') AS Latest_Birth

FROM tng_people

WHERE lastname <> '' AND gedcom = 'savenije'

GROUP BY lastname

ORDER BY lastname
 
224 156  The wife is male  Marriages where the wife is male and therefore a mistake might have been made
Huwelijken waar de echtgenote is mannelijk en mogelijk verkeerd zou kunnen zijn ingevoerd 
SELECT f.familyID, h.personID as personID1, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
w.sex = "M"
)
ORDER BY familyID 
225 288  Time line events  Zaken die in het verleden gebeurden.   SELECT evyear, evdetail
FROM `tng_timeline` ORDER BY `tng_timeline`.`evyear` ASC 
226 21  Tweelingen  Personen, geboren in het astrologische teken tweelingen (Gemini): 21 mei-21 juni
Individuals born in astrological sign gemini: 21 MAY - 21 JUNTweelingen 21 mei - 21 Juni Ik vergaar kennis
Kontakten en kommunikatie
Het omgaan met informatie
Uitwisseling met de andere mens
Heerser Mercurius;
- hoe je manier van praten, schrijven en telefoneren is
- hoe je met je direkte omgeving omgaat
- hoe je iets overdenkt en leert van de dagelijkse ervaringen
Gemini is the Mutable-Air sign. Using their communicative skills, Geminis adapt to any situation they encounter. However, they must learn to speak with candor instead of simply repeating what others want to hear. Clever-tongued Geminis develop the amazing ability to obscure the facts in their stimulating and imaginative chatter. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21)) ORDER BY lastname, firstname, personID;  
227 225  Unused place names  Ongebruikte plaatsnamen  SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = 'savenije'
AND place
IN (

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

SELECT gedcom, birthplace AS place
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'savenije'
) AS p
USING ( gedcom, place )
WHERE pl.gedcom = 'savenije'
AND isnull( p.place )
228 285  Verandering aangebracht door Lidewij Piek    SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "lidewijpiek"
ORDER BY `tng_people`.`changedate` DESC 
229 292  Verandering gemaakt door Ingrid Bron    SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "ingridbron"
ORDER BY `tng_people`.`changedate` DESC 
230 281  Veranderingen aangebracht door Jaap    SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate FROM `tng_people` WHERE changedby = "databoss" ORDER BY `tng_people`.`changedate` DESC 
231 280  Veranderingen aangebracht door Joos    SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "joos"
ORDER BY `tng_people`.`changedate` DESC 
232 282  Veranderingen aangebracht door Peer    SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "Peer Notermans"
ORDER BY `tng_people`.`changedate` DESC 
233 290  Veranderingen gemaakt door Henk van der Meer  Changes made by Henk  SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "hmkvdm"
ORDER BY `tng_people`.`changedate` DESC 
234 44  Veterans  An overview of veterans (at least if you added some)  SELECT tng_people.living, lnprefix, suffix, tng_people.branch,lastname, firstname,birthdate,deathdate,e104.eventdate as eventdate104,e104.eventplace as eventplace104,e104.info as info104, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_events e104 ON tng_people.personID = e104.persfamID AND tng_people.gedcom = e104.gedcom AND e104.eventtypeID = "104" WHERE (e104.eventplace LIKE "%%") ORDER BY lastname 
235 30  Vissen  Personen, geboren in het astrologische teken Vissen (Pisces): 20.02.-20.03.
Individuals born in astrological signVissen 20 februari - 22 maart Ik offer en droom verder Afzondering. Eenheidsbeleving. Illusies
Helderziendheid. Aanvoelend vermogen. Geheimen. Fantasie
Dromen. Hypnose, Verborgen vijanden. Onzichtbare tegenwerking
Heerser; Neptunus
- geeft aan daar waar je zonder eigenbelang anderen kunt helpen
- hoe je omgaat met spirituele waarden
- daar waar je "de mist in kan raken", en erg beïnvloedbaar bent -
- hoe iemand intuïtief zijn juiste draai in het leven vindt.
Pisces is the Mutable-Water sign. Pisceans adapt emotionally to the influence of their environment. Often painfully shy, they are adept at imitating the mannerisms of other people as a way of hiding their own personality. Pisceans can too easily become victims of their considerable ability to identify with the personality and problems of other people, since it severely restricts the development of their own personality traits and talents. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID; 
236 29  Waterman  Personen, geboren in het astrologische teken Waterman (Aquarius): 21.01.-19.02.
Individuals born in astrological sign aquarius: 21 JAN - 19 FEB

Waterman . 21 januari - 20 februari Ik ben ik, en jij bent jij
Vrienden. Geestverwanten. Persoonlijke vrijheid.
Nieuwe strukturen op basis van gelijkheid voor iedereen
Politieke partijen. Klubverband
Heerser Uranus;
- geeft de behoefte tot veranderen, verbreken en doorbreken aan van bestaande normen en
grenzen, zodat je je als individu kunt ontwikkelen.
- hoe je oorspronkelijk en origineel wilt zijn
- mensen die je helpen bij het vervullen van je wensen, die ten dienste staan van de mensheid
Aquarius is the Fixed-Air sign. Aquarians are born looking for ideologies to which they can stubbornly cling. They refuse to budge whenever an issue involves what they believe to be a "matter of principle. " As in the case of the Aquarian Abraham Lincoln, society greatly benefits when these principles happen to be noble ones. 

SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19)) ORDER BY lastname, firstname, personID; 
237 25  Weegschaal  Personen, geboren in het astrologische teken Weegschaal (Libra): 24.9.-23.10.
Individuals born in astrological sign libra: 24 SEP - 23 OCT Weegschaal 23 september - 23 oktober Ik verenig
Relaties
Samenwerkingsverbanden
Afspraken en kontrakten
Heerser Venus;
- zegt iets over de manier waarop je in staat bent liefde te geven of te ontvangen,
- hoe je tegendelen verenigt en in hoeverre je bereid bent water bij de wijn te doen

Libra is the Cardinal-Air sign. Librans accomplish things because they intellectually evaluate what needs to be done, and then they charm others into cooperating with them to achieve the goal. By unselfishly sharing the success of accomplishment with those who assisted, Librans continue to engender the cooperative efforts of others. They have to learn how to deal with confrontations. Fear of hurting others or avoidance of hostile situations can keep them from pursuing their goals. 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID; 
238 128  Wezen zonder partner en kinderen, Orphans without partner and childeren  Mensen die dus aan niemand verbonden zijn.
Open de links in het rapport alstublieft in een nieuw venster.
Persons who are connected to nobody.
Open the links in the report please in a new window. 
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changedby FROM tng_people AS p
LEFT OUTER JOIN tng_children AS c ON (p.personID=c.personID AND p.gedcom=c.gedcom)
LEFT OUTER JOIN tng_families AS f1 ON (p.personID=f1.husband AND p.gedcom=f1.gedcom)
LEFT OUTER JOIN tng_families AS f2 ON (p.personID=f2.wife AND p.gedcom=f2.gedcom)
WHERE c.personID IS NULL AND f1.husband IS NULL AND f1.wife IS NULL AND f2.husband IS NULL AND f2.wife IS NULL
ORDER BY p.changedate, p.lastname, p.firstname, p.birthdate DESC;  
239 127  Wezen, Orphans  Personen zonder ouders, geoordend volgens de laatste invoer eerst.
People without any parents, ordered according to the last input.  
SELECT personID, firstname AS first_name,lnprefix AS tussenvoegsel,lastname AS last_name, birthdate AS Geboortedatum, birthplace AS Geboorteplaats, changedate AS Veranderdatum, gedcom, changedby FROM tng_people WHERE famc= "" order by changedate DESC  
240 310  Wrong burial dates  foute begraafdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, burialdate, burialdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( burialdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( burialdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( burialdate, '%Y' ) IS NULL
AND NOT (burialdate = "y" or burialdate = "n"
) and NOT (burialdate like "Abt %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Cal %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Est %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Bef %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Abt %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Aft %" and length (burialdate) = 8 and year(burialdatetr) != 0000)

and NOT (burialdate like "Abt %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Cal %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Est %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Bef %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Abt %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Aft %" and length (burialdate) = 7 and year(burialdatetr) != 0000)

and NOT (burialdate like "Abt %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Cal %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Est %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Bef %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Aft %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)

and NOT (burialdate like "Abt %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Cal %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Est %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Bef %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Aft %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)

and NOT (burialdate like "Bet %" and length (burialdate) = 17 and (year(burialdatetr) != 0000 or month(burialdatetr) != 00 or day(burialdatetr) != 00))

and NOT (burialdate like "Bet %" and length (burialdate) = 31 and (year(burialdatetr) != 0000 or month(burialdatetr) != 00 or day(burialdatetr) != 00))


ORDER BY `tng_people`.`burialdate` ASC 
241 311  Wrong death dates  Foute overlijdensdatums  SELECT gedcom, personID, lnprefix, lastname, firstname, deathdate, deathdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( deathdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( deathdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( deathdate, '%Y' ) IS NULL
AND NOT (deathdate = "y" or deathdate = "n"
) and NOT (deathdate like "Abt %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Cal %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Est %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Bef %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Abt %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Aft %" and length (deathdate) = 8 and year(deathdatetr) != 0000)

and NOT (deathdate like "Abt %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Cal %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Est %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Bef %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Abt %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Aft %" and length (deathdate) = 7 and year(deathdatetr) != 0000)

and NOT (deathdate like "Abt %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Cal %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Est %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Bef %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Aft %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)

and NOT (deathdate like "Abt %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Cal %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Est %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Bef %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Aft %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)

and NOT (deathdate like "Bet %" and length (deathdate) = 17 and (year(deathdatetr) != 0000 or month(deathdatetr) = 00 or day(deathdatetr) = 00))
and NOT (deathdate like "Bet %" and length (deathdate) = 31 and (year(deathdatetr) != 0000 or month(deathdatetr) = 00 or day(deathdatetr) = 00))


ORDER BY `tng_people`.`deathdate` ASC 
242 312  Wrong divorce dates  Foute scheidingsdatums  SELECT gedcom, familyID, husband, wife, divdate, divdatetr, changedby
FROM tng_families
WHERE STR_TO_DATE( divdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( divdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( divdate, '%Y' ) IS NULL
AND NOT (divdate = "y" or divdate = "n"
) and NOT (divdate like "Abt %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Cal %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Est %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Bef %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Abt %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Aft %" and length (divdate) = 8 and year(divdatetr) != 0000)

and NOT (divdate like "Abt %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Cal %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Est %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Bef %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Abt %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Aft %" and length (divdate) = 7 and year(divdatetr) != 0000)

and NOT (divdate like "Abt %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Cal %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Est %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Bef %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Aft %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)

and NOT (divdate like "Abt %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Cal %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Est %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Bef %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Aft %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)


and NOT (divdate like "Bet %" and length (divdate) = 31 and (year(divdatetr) != 0000 or month(divdatetr) != 00 or day(divdatetr) != 00))
and NOT (divdate like "Bet %" and length (divdate) = 17 and (year(divdatetr) != 0000 or month(divdatetr) != 00 or day(divdatetr) != 00))


ORDER BY `tng_families`.`divdate` ASC 
243 313  Wrong event datums  Foutieve gebeurtenisdatums  SELECT gedcom, persfamID, eventtypeID, eventdate, eventdatetr, eventplace, age, agency, cause, addressID, parenttag, info
FROM tng_events
WHERE STR_TO_DATE( eventdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( eventdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( eventdate, '%Y' ) IS NULL
AND NOT (eventdate = "y" or eventdate = "n"
) and NOT (eventdate like "Abt %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Cal %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Est %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Bef %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Abt %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Aft %" and length (eventdate) = 8 and year(eventdatetr) != 0000)

and NOT (eventdate like "Abt %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Cal %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Est %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Bef %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Abt %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Aft %" and length (eventdate) = 7 and year(eventdatetr) != 0000)

and NOT (eventdate like "Abt %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Cal %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Est %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Bef %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Aft %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)

and NOT (eventdate like "Abt %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Cal %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Est %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Bef %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Aft %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)

and NOT (eventdate like "Bet %" and length (eventdate) = 17 and (year(eventdatetr) != 0000 OR month(eventdatetr) != 00 OR day(eventdatetr) != 00))
and NOT (eventdate like "Bet %" and length (eventdate) = 31 and (year(eventdatetr) = 0000 OR month(eventdatetr) = 00 OR day(eventdate) = 00))


ORDER BY `tng_events`.`eventdate` ASC 
244 306  Wrong family links 1    SELECT familyID, personID, gedcom FROM tng_children c
WHERE NOT EXISTS (SELECT * FROM tng_people p WHERE c.personID = p.personID and c.gedcom=p.gedcom)
OR NOT EXISTS (SELECT * FROM tng_families f WHERE c.familyID=f.familyID and c.gedcom=f.gedcom)
 
245 314  Wrong marriage dates  Foute trouwdatums  SELECT gedcom, familyID, husband, wife, marrdate, marrdatetr, changedby
FROM tng_families
WHERE STR_TO_DATE( marrdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( marrdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( marrdate, '%Y' ) IS NULL
AND NOT (marrdate = "y" or marrdate = "n"
) and NOT (marrdate like "Abt %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Cal %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Est %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Bef %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Abt %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Aft %" and length (marrdate) = 8 and year(marrdatetr) != 0000)

and NOT (marrdate like "Abt %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Cal %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Est %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Bef %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Abt %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Aft %" and length (marrdate) = 7 and year(marrdatetr) != 0000)

and NOT (marrdate like "Abt %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Cal %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Est %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Bef %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Aft %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)

and NOT (marrdate like "Abt %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Cal %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Est %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Bef %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Aft %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)

and NOT (marrdate like "Bet %" and length (marrdate) = 17 and (year(marrdatetr) != 0000 or month(marrdatetr) = !00 or day(marrdatetr) != 00))
and NOT (marrdate like "Bet %" and length (marrdate) = 31 and (year(marrdatetr) != 0000 or month(marrdatetr) = !00 or day(marrdatetr) != 00))


ORDER BY `tng_families`.`marrdate` ASC
 
246 287  Wrong marriage or divorce dates  Foutief ingevoerde huwelijks of scheidingsdatums  SELECT familyID, husband, wife, marrdate, marrdatetr, changedby FROM tng_families
WHERE (Length( marrdate ) >4 AND NOT ( marrdate LIKE "Abt%" OR marrdate LIKE "Cal %" OR marrdate LIKE "Bef %" OR marrdate LIKE "Aft %" OR marrdate LIKE "Est %" OR marrdate LIKE "Bet %" OR marrdate LIKE "% BC" ) AND marrdatetr LIKE "%-00-00")OR (Length( divdate ) >4 AND NOT ( divdate LIKE "Abt%" OR divdate LIKE "Cal %" OR divdate LIKE "Bef %" OR divdate LIKE "Aft %" OR divdate LIKE "Est %" OR divdate LIKE "Bet %" OR divdate LIKE "% BC" ) AND divdatetr LIKE "%-00-00") or
( divdate LIKE "%?%" OR divdate LIKE "%?%"); 
247 172  Wrong place names for FAMIILIES  Places to families, which are NOT a member of the places table (check for data plausibility)

Gezinnen in plaatsen die niet in de plaatsnamen lijst staan (controle op plausibiliteit) 
SELECT familyID, husband, wife, "Place of marriage" AS Kind_of_place, marrplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.marrplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND marrplace<>""
UNION
SELECT familyID, husband, wife, "Place of divorce" AS Kind_of_place, divplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.divplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND divplace<>""
ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED);  
248 171  Wrong place names for PERSONS  Places to persons, which are NOT a member of the places table (check for data plausibility)

Mensen in plaatsen die niet in de plaatsnamen lijst staan (controle op plausibiliteit) 
SELECT personID, lastname, firstname, "Place of birth" AS Kind_of_place, birthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.birthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND birthplace<>""
UNION
SELECT personID, lastname, firstname, "Place of birth" AS Kind_of_place, altbirthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.altbirthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND altbirthplace<>""
UNION
SELECT personID, lastname, firstname, "Place of death" AS Kind_of_place, deathplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.deathplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND deathplace<>""
UNION
SELECT personID, lastname, firstname, "Place of burial" AS Kind_of_place, burialplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.burialplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND burialplace<>""
UNION
SELECT personID, lastname, firstname, "Place of event" AS Kind_of_place, eventplace AS place_detail, living, p.gedcom FROM tng_events AS e LEFT JOIN tng_places AS pl ON (e.eventplace=pl.place AND e.gedcom=pl.gedcom) LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE ISNULL(place) AND eventplace<>""
ORDER BY lastname, firstname;  





Calendar

Ik vind deze site geweldig en wil graag financieel helpen het in stand te houden

Klik alstublieft hier om te lezen
over de veranderde situatie

I like this service very much and I want to donate money    

 


This site powered by The Next Generation of Genealogy Sitebuilding ©, written by Darrin Lythgoe 2001-2017.