Charting Companion “SQL query” feature — Part 3

Published on: 1 October 2023

All burials in a certain place, ex: “CT” Connecticut

select PIN, FROM_DATE, EV_TYPE,
(select NAME from PERSONS as P where P.PIN = E.PIN) as NAME,
(select PLACE_NAME from PLACES as PL where PL.PLACE_NO = E.PLACE_NOB and PL.PLACE_NO > 0 ) as PLACE
from EVENTS as E where E.EV_TYPE = 4 and PLACE like "%CT%"

Burial before Death, or too long after

select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_DEATH,
(select FROM_DATE from EVENTS as E2 where E2.PIN = E1.PIN and E2.EV_TYPE = 4) as DATE_BURIAL
from EVENTS as E1
where E1.EV_TYPE = 3 and DATE_BURIAL > 0 and (DATE_DEATH / 10000 > DATE_BURIAL / 10000 or (DATE_BURIAL - DATE_DEATH) > 10000)
order by DATE_BURIAL, NAME

Persons dead, with no Burial

select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_DEATH,
(select count (*) from EVENTS as E2 where E2.PIN = E1.PIN and E2.EV_TYPE = 4) as BURIAL_COUNT
from EVENTS as E1
where E1.EV_TYPE = 3 and BURIAL_COUNT = 0
order by DATE_DEATH

All men eligible for WW1 draft

select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
(select SEX from PERSONS as P where P.PIN = E1.PIN) as SEX,
E1.FROM_DATE as DATE_BIRTH
from EVENTS as E1
where E1.EV_TYPE = 1 and DATE_BIRTH >= 18720000 and DATE_BIRTH <= 19000000 and SEX = 1

Show children with adoptive + bio parents

select P.NAME, E.EV_TYPE, E.EV_SEQNO as ADOPTER, P2.NAME as NAME_ADOPTER, P3.NAME as NAME_FATHER_BIOLOGICAL
from
(((PERSONS as P inner join EVENTS as E on P.PIN = E.PIN)
inner join PERSONS as P2 on P2.PIN = E.EV_SEQNO)
inner join PERSONS as P3 on P3.PIN = P.FATHER_PIN)
where E.EV_TYPE = 8

Search for an exact match on Surname

select NAME from persons where NAME like "%" || x'05' || "Bouvier" || x'06' || "%"

Search for an exact match on Given name

select NAME from persons where NAME like "%" || x'02' || "Lee" || x'03' || "%"

Death event but zero death date

select PIN, EVENTS.FROM_DATE,
(select PERSONS.NAME from PERSONS where PIN = EVENTS.PIN) as NAME
from EVENTS
where EVENTS.EV_TYPE = 3 and EVENTS.FROM_DATE = 0

Here’s how to find a SMITH who married a JONES:

select P1.NAME, P2.NAME
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where P1.NAME like "%SMITH%" and P2.NAME like "%JONES%"

To list everyone and their parents:

select P.NAME, P1.NAME, P2.NAME
from ((PERSONS as P inner join PERSONS as P1 on P.FATHER_PIN = P1.PIN)
inner join PERSONS as P2 on P.MOTHER_PIN = P2.PIN)
where P.FATHER_PIN > 0 or P.MOTHER_PIN > 0
order by P1.NAME, P2.NAME

List events + Places

select PIN, EVENTS.FROM_DATE,
(select PLACES.PLACE_NAME from PLACES where PLACE_NO > 0 and PLACE_NO = EVENTS.PLACE_NOB)
from EVENTS
where EVENTS.EV_TYPE = 3 and EVENTS.FROM_DATE = 0

Search for no Surname

select NAME from persons where NAME like "%" || x'05' || x'06' || "%"

Search for no Given name

select NAME from persons where NAME like "%" || x'02' || x'03' || "%"