Charting Companion “SQL query” feature — Part 2

Published on: 24 September 2023

Formatting

If an alias is assigned (“as…”) that starts with “NAME”, Charting Companion will understand that it is a name column, and will re-format it accordingly. Charting Companion stores the names in an internal format that includes binary delimiters of the name parts.

The following aliases can be used. The alias need only begin with these codes, and can include additional characters. Ex: NAME1, NAME_PARENT, etc.

Alias (as …)	Converted to
NAME	Full name
FIRST	Given name
LAST	Surname
DATE	Date (YYYYMMDD)
DAY	Day part of date
MONTH	Numeric month part of date
YEAR	Year part of date

SQL commands – examples

Retrieve all persons and their parents

select P.NAME, P1.NAME as NAME_father, P2.NAME as NAME_mother
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

Display all couples

select P1.NAME, P2.NAME, EVENTS.EV_TYPE
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where EVENTS.DUAL_KEY = 1 and P1.SEX = 1

List persons with no Sex specified

select PIN, NAME from PERSONS where SEX < 1 or SEX > 2

Find couples with no children

Three steps of preparation; run once per GEDCOM import:

drop table COUPLES

click “Execute” (ignore error if table does not exist yet)

create table COUPLES (HUSB int, WIFE int, SEX int)

click “Execute”

INSERT INTO COUPLES (HUSB, WIFE, SEX)
select
EVENTS.PIN,
EVENTS.EV_SEQNO,
(select PERSONS.SEX from PERSONS where PIN = EVENTS.PIN) as SEX1
from EVENTS
where EVENTS.DUAL_KEY = 1 and SEX1 = 1

click “Execute”

Shows couples with no children (run as often as you wish):

select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)

Display all children and their parents

select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)
order by P2.NAME

Display contents of COUPLES

select P1.NAME, P2.NAME
from ((COUPLES inner join PERSONS as P1 on COUPLES.HUSB = P1.PIN)
inner join PERSONS as P2 on COUPLES.WIFE = P2.PIN)
order by P1.NAME

Daughters with different surname than father

Could be an indication that they are recorded with their married name.

select PIN, NAME, SURNAME as SURNAME1,
(select SURNAME from PERSONS as P2 where P2.PIN = P1.FATHER_PIN) as SURNAME2
from PERSONS as P1
where SEX = 2 and P1. FATHER_PIN > 0 and SURNAME1 <> SURNAME2

Extract all Births

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 = 1

Calculate life spans

select E1.PIN, E1.FROM_DATE, E2.FROM_DATE, (E2.FROM_DATE - E1.FROM_DATE)/10000 as Life_Span,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME
from EVENTS as E1
inner join EVENTS as E2 on E2.PIN = E1.PIN and E2.EV_TYPE = 3
where E1.EV_TYPE = 1 and E1.FROM_DATE > 0 and E2.FROM_DATE > 0
order by Life_Span

List everyone whose death date is before birth date

select PIN, NAME,
(select FROM_DATE from EVENTS as E1 where E1.PIN = P.PIN and E1.EV_TYPE = 1) as [DATE_BIRTH],
(select FROM_DATE from EVENTS as E2 where E2.PIN = P.PIN and E2.EV_TYPE = 3) as [DATE_DEATH]
from PERSONS as P
where DATE_BIRTH > 0 and DATE_DEATH > 0 and DATE_DEATH < DATE_BIRTH
order by DATE_BIRTH

List everyone’s birth, death + place

Makes Charting Companion a handy, general-purpose extraction & reporting tool

select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_BIRTH,
(select PLACE_NAME from PLACES as PL where E1.PLACE_NOB > 0 and PL.PLACE_NO = E1.PLACE_NOB and E1.EV_TYPE = 1) as Birth_Place,
E2.FROM_DATE as DATE_DEATH,
(select PLACE_NAME from PLACES as PL where E2.PLACE_NOB > 0 and PL.PLACE_NO = E2.PLACE_NOB) as Death_Place
from EVENTS as E1
left join EVENTS as E2 on E2.PIN = E1.PIN and E2.EV_TYPE = 3
where E1.EV_TYPE = 1

Total number of children in each family

select P2.NAME,P3.NAME, count(*) as kid_count
from (((COUPLES inner join PERSONS as P1 on COUPLES.HUSB = P1.FATHER_PIN and COUPLES.WIFE = P1.MOTHER_PIN)
inner join PERSONS as P2 on P2.PIN = COUPLES.HUSB)
inner join PERSONS as P3 on P3.PIN = COUPLES.WIFE)
group by P2.PIN, P3.PIN

Notes

“Order by NAME” will sort by first name, as this is how the name is stored in the database. To sort by surname/given name, add a column with “as FIRST” alias, and add:

order by SURNAME, FIRST

When clicking on a column header, CC will sort as plain text, which puts ’10’ before ‘2’.

DB Browser for SQLite

DB Browser for SQLite is a free utility for general-purpose inspection and exploration of SQLite databases. You can download it from here:

https://sqlitebrowser.org/