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 51 to 88 of 88
# | reportID | Report Name | reportdesc | sqlselect | active |
---|---|---|---|---|---|
51 | 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; |
1 |
52 | 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; |
1 |
53 | 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; |
1 |
54 | 18 | Individuals: frequency of zodiacal signs | Frequency of the zodiacal sign for individuals in the database. | 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 Sign, COUNT(*) AS Count FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" GROUP BY Sign; | 1 |
55 | 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; |
1 |
56 | 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 |
1 |
57 | 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 | 1 |
58 | 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 ; |
1 |
59 | 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 ; |
1 |
60 | 226 | List eventypes | List even types with eventypeID | SELECT eventtypeID, tag, description, display, keep, ordernum, type FROM `tng_eventtypes` ORDER BY `eventtypeID` ASC |
1 |
61 | 173 | Media statistics | Types of media by frequency | 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; |
1 |
62 | 130 | Mother is missing | Individuals with father, but without mother (mother is missing) | 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; |
1 |
63 | 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 |
64 | 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 |
65 | 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 |
66 | 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 |
67 | 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 |
68 | 128 | Orphans without partner and children | Persons who are connected to nobody. Open the links in the report 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; |
1 |
69 | 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 |
70 | 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 |
71 | 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 |
72 | 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 |
73 | 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 |
74 | 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 |
75 | 131 | Places ordered by the last entered | Places by last entered | SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY ID DESC; |
1 |
76 | 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 | |
77 | 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 |
78 | 133 | Second place name levels ordered by frequency | All occurring second place name levels, including frequency, ordered by frequency | 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; | 1 |
79 | 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 |
80 | 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 |
81 | 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 |
82 | 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 |
83 | 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 |
84 | 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 |
85 | 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 |
86 | 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 |
87 | 135 | Third place name levels ordered by frequency | All occurring third place name levels, including frequency, ordered by frequency | 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; | 1 |
88 | 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 ) ) |
1 |
«Prev 1 2