Beschrijving: 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
Treffers 151 t/m 200 van 212 » Komma gescheiden CSV bestand
| # | reportID | Rapportnaam | reportdesc | sqlselect | active |
|---|---|---|---|---|---|
| 151 | 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; |
1 |
| 152 | 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; | 1 |
| 153 | 177 | Media without coordinates | Media zonder coordinaten | SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="" OR latitude="" ORDER BY description; | 1 |
| 154 | 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 |
1 |
| 155 | 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; | 1 |
| 156 | 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; | 1 |
| 157 | 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; | 1 |
| 158 | 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; | 1 |
| 159 | 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); | 1 |
| 160 | 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; |
1 |
| 161 | 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 |
1 |
| 162 | 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; |
1 |
| 163 | 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 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 |
1 |
| 164 | 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 |
1 |
| 165 | 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 ) |
1 |
| 166 | 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 |
1 |
| 167 | 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; |
1 |
| 168 | 235 | People with aproximate birthdates in the provinces of Groningen and Drenthe | Mensen met een "ongeveer" geboortedag in Groningen en Drenthe (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 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 ) < "1911" AND YEAR( birthdatetr ) > "1700") ) OR ( birthplace LIKE "%Drenthe" AND YEAR( birthdatetr ) < "1903" AND YEAR( birthdatetr ) > "1700" ) ) ) ORDER BY ID, lastname, firstname, personID |
1 |
| 169 | 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; |
1 |
| 170 | 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; |
1 |
| 171 | 159 | People without a default image | Mensen zonder een standaard klikplaatje | SELECT lastname, firstname, personid, gedcom FROM ( SELECT p.lastname, p.firstname, p.personid, p.gedcom, MAX(ml.defphoto) AS mdp, COUNT(ml.medialinkid) AS n FROM tng_people AS p, tng_medialinks AS ml, tng_media AS m WHERE ml.personid = p.personid AND ml.gedcom = p.gedcom and ml.mediaid = m.mediaid AND m.gedcom = p.gedcom AND m.mediatypeid = 'photos' GROUP BY p.personid ) AS tmp WHERE n > 0 AND mdp != 1 |
1 |
| 172 | 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; |
1 |
| 173 | 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 |
1 |
| 174 | 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; |
1 |
| 175 | 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; |
1 |
| 176 | 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; |
1 |
| 177 | 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" ); |
1 |
| 178 | 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; |
1 |
| 179 | 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 |
1 | |
| 180 | 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 <> "" ) |
1 |
| 181 | 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; | 1 | |
| 182 | 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; | 1 |
| 183 | 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; | 1 |
| 184 | 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; | 1 | |
| 185 | 26 | Schorpioen | Personen, geboren in het astrologische teken Schorpioen (Skorpius): 24.10.-22.11. Individuals born in astrological sign scorpio: 24 OCT - 22 NOV 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; | 1 |
| 186 | 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; |
1 |
| 187 | 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); |
1 |
| 188 | 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); |
1 |
| 189 | 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 Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY Number | 1 |
| 190 | 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; | 1 |
| 191 | 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); |
1 |
| 192 | 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; |
1 |
| 193 | 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; |
1 |
| 194 | 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; | 1 |
| 195 | 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; |
1 |
| 196 | 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; |
1 |
| 197 | 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; |
1 |
| 198 | 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; |
1 |
| 199 | 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; | 1 |
| 200 | 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 |
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 | 1 |
