Favourite SQL queries

[Nick Hunter]

Hi Nick,

Thanks for the query syntax to combine the layers and families databases, it does list all the layered families and show which layer each family is in.

SELECT F.[Description], L.[Description] Layer
FROM Layers L, Families F
WHERE L.

Code: Select all

 = F.[Layer]

By experimenting I have discovered that it is also possible to select the families on one specific layer, in the following example layer number 1.

SELECT F.[Description], L.[Description] Layer
FROM Layers L, Families F
WHERE L.[Code] = F.[Layer] AND L.[Code] = 1

I hope other users find this useful. Could some of these unusual query syntax be added to the help file, or should we have a post section where users can post any unusual query syntax they have found useful?

John Wood

[Nick Hunter]

Hi John,

I think that's a great idea.

Please post in this thread any SQL queries that you find useful.

[laschapas]

Hi all,

To give quick access to all references to a particular source document use the following query. The document code will be the one found under the "Code" column of the "Source documents" window and will be unique to your database. It could be a particular church baptism records, or a census year, or what your great aunt told you. Simply replace the number 16 with your source document code. Copy the example below to a notepad and save with a .sql extension, then load it into the SQL Query by clicking the "Load from file" icon under File on the SQL Query window.

--List Family/Person record, type, code, field name & notes for Source Document 16
SELECT S.RecordType, S.RecordCode, S.FieldName, S.Notes
FROM Sources S, Documents D
WHERE S.Document = D.Code AND D.Code = 16
ORDER BY S.RecordCode

Hope you find this useful,
John Wood

[laschapas]

Hi all,

Searching for a particular church I found that the search engine will not accept the apostrophe character, as in St. Paul's. It will however accept the full stop after St. So how do you select only London, St. Paul's church from the list below:-

London St. Paul
London St Paul
London St. Paul's
London St Paul's
London, St. Paul's
St. Paul's, London
London St Anywhere
Clondon, St. Paul
Londoning, St Paul

The following SQL query will select only the first four places from the above list as family marriage places. There is a SPACE after the word London to eliminate Londoning but this also eliminates London, St. Paul's because of the COMMA after London. It also eliminates St. Paul's, London because there is no SPACE after London.

SELECT Families.[Description], Families.[Marriage place]
FROM Families
WHERE ( Families.[Marriage place] LIKE '%London %' ) AND ( Families.[Marriage place] LIKE '%Paul%' )

The query can be extended to catch London, St. Paul's & increase the hit to the first five as follows:-

SELECT Families.[Description], Families.[Marriage place]
FROM Families
WHERE ( Families.[Marriage place] LIKE '%London %' ) AND ( Families.[Marriage place] LIKE '%Paul%' ) OR ( Families.[Marriage place] LIKE '%London,%' ) AND ( Families.[Marriage place] LIKE '%Paul%' )

To capture the sixth entry the search has to be extended again as follows :-

SELECT Families.[Description], Families.[Marriage place]
FROM Families
WHERE ( Families.[Marriage place] LIKE '%London %' ) AND ( Families.[Marriage place] LIKE '%Paul%' ) OR ( Families.[Marriage place] LIKE '%London,%' ) AND ( Families.[Marriage place] LIKE '%Paul%' ) OR ( Families.[Marriage place] LIKE '% London%' ) AND ( Families.[Marriage place] LIKE '%Paul%' )

Is this now becoming a search too far? I hope you find this useful and/or comment provoking.

John Wood

[Nick Hunter]

Hi John,

Try doubling up the apostrophe '', eg. LIKE '%St. Paul''s%'

[laschapas]

Hi Nick,

This is probably getting too heavy into SQL but in your example does the first quote tell the search engine to treat the second quote as part of the search string?

Two things I have realized in this query experiment are:-

1. Two single quotes together look exactly like a double quote in proportionally spaced fonts.

2. The search engine uses the single quote to enclose search items, therefore using it in text fields to show possession creates an extra problem to remember when creating queries.

John Wood

[Nick Hunter]

That's right. This is quite common anywhere that a symbol is used as a delimiter but you want it treated as a literal.

1 & 2 - That's just the way it works I'm afraid.

[laschapas]

Hi all,

I wanted to search for all the Notes with a Privacy Level of 66 from Families, but restrict the search to Families on Layer 2. I don't use Privacy Level 66 for People Notes so the search does not check that it is only searching Family Notes. Here is the query :-

--Notes with a Privacy Level of 66 from Families on Layer 2
SELECT F.[Description], N.[Description], N.[Notes]
FROM Layers L, Families F, Notes N
WHERE L.

Code: Select all

 = F.[Layer] AND L.[Code] = 2 AND N.[RecordCode] = F.[Code] AND 
N.[PrivacyLevel] = 66

The result shows the description field of the Family, the Description field of the Notes, and the first line of the text of the Notes. If you export the result to a text file you get all the text from the Notes.

Please Nick can you check that what I have said is correct.

John Wood

[laschapas]

Hi all,

The "Editing data in bulk" help page suggests a possible use for this feature but does not include an example. Here is the paragraph from the help page:-

"For example, if you have added a new user-defined field to the people table, you may want to add the data for the new field for every person in the database. To do this you would apply an appropriate SQL query which would include the new field so that every person record is displayed in the results list but possibly only showing the new field and the "Description" field."

Here is an example of how I use it. I have a User Defined Field for Families of "On 1911 Census", which I use to record where people are living on the 1911 census. The following query lists all the families with any information in the "On 1911 Census" field and sorts them by address. I can then see if I have entered addresses in a consistent form. I can also see who was living near to who in 1911.

--Where are families in 1911?
SELECT Families.[Description], Families.[On 1911 Census]
FROM Families
WHERE ( Families.[On 1911 Census] IS NOT NULL )
ORDER BY Families.[On 1911 Census]

By changing the search from "NOT NUL" to"dudley" I can check which families lived in Dudley in 1911, see below:-

--Who lived in Dudley in 1911?
SELECT Families.[Description], Families.[On 1911 Census]
FROM Families
WHERE ( UPPER(Families.[On 1911 Census]) LIKE '%DUDLEY%' )
ORDER BY Families.[On 1911 Census]

Another variation is to check who lived in Dudley in 1901 and was still in Dudley in 1911, see the following query:-

--Who lived in Dudley in 1901 & 1911?
SELECT Families.[Description], Families.[On 1901 Census], Families.[On 1911 Census]
FROM Families
WHERE ( UPPER(Families.[On 1911 Census]) LIKE '%DUDLEY%' )
AND ( UPPER(Families.[On 1901 Census]) LIKE '%DUDLEY%' )

There are lots of other permutations of these simple searches. You don't have to be editing data in bulk to use these queries, but if you do resort to editing data in bulk please read the WARNING on the help page.

I hope you find this useful.

John Wood

[laschapas]

Hi all,

You backup your database, by taking a copy, once a week or once a month. You then start to worry about what you have changed. You would like to go back to the old database for some of your data, but not it all. The following queries will show you which person & family records have changed. Change the date following the JDATE to when you last took a copy of your database.

--People changed since backup taken
SELECT People.[Description], People.[Change date]
FROM People
WHERE ( People.[Change date] > JDATE('01 jun 2009') )
ORDER BY People.[Change date]

--Families changed since backup taken
SELECT Families.[Description], Families.[Change date]
FROM Families
WHERE ( Families.[Change date] > JDATE('20 jun 2009') )
ORDER BY Families.[Change date]

If you want to compare the old & new family records side by side, export the specific families from the two versions of your database as GEDCOM files. Then import the two GEDCOM files into a new database.

Hope you find this useful.
John Wood

[jhunt02]

This query can be used in Kith & Kin to show all people who are in limbo. Furthermore, as you add the people to their families, they immediately disappear from the query result list ("live" update), so you can tell how far along you've gotten.

-- List all people who are in limbo
select * from People
where Code not in
(select P.Code from People P, Links L where
(P.Code = L.ChildMarr and L.RecType = 'F') or (P.Code = L.PersFam and L.RecType = 'P'));

[laschapas]

Hi all,

Further to the first post in this section on 14 May to combine Families and Layers I have another permutation to add. If you need to use the Layer feature in the Query Family window you need to use the number of the layer not the name you have given the layer. The following mod. to the previous syntax shows the layer number as well as the layer name for each family.

SELECT F.[Description], F.[Layer], L.[Description] Layer
FROM Layers L, Families F
WHERE L.

Code: Select all

 = F.[Layer]

Hope you find this useful,
John Wood

[laschapas]

Hi all,

Here is a query I use to list the picture description & file name for a specific set of pictures I have linked to my database. You could list all your pictures and filter the specific set by copying the list into a spreadsheet. First a bit of background as to how I set up my database. Under the Pictures folder that the system creates automatically I have sub-folders for census, certificates, BDM Images, Reports, and others. Under the census sub-folder I have other sub-folders for each census year, ie. 1841, 1851 etc.. Also under each Family in Kith&KinPro I have user defined fields for each census year. In the Notes field for each Source Reference census year, I add my transcription of the census page for that family for that year. I also add the image of the page, having first copied the image into the census year sub-folder.

If you are still with me, the following SQL query lists the description & file name of all pictures linked to Source References of the 1881 census.

--1881 Picture Files
SELECT Pictures.[Description], Pictures.[Filename]
FROM Pictures
WHERE ( UPPER(Pictures.[RecordType]) LIKE '%S%' )
AND ( UPPER(Pictures.[Filename]) LIKE '%CENSUS\1881%' )

Sorry about the long winded description. I hope you can follow it, and you find it useful.

John Wood

[laschapas]

Hi all,

Here is a variation to the query I posted on the 14 May 2009 under the subject "List all references to a particular source document" First a recap on what I said then:-

To give quick access to all references to a particular source document use the following query. The document code will be the one found under the "Code" column of the "Source documents" window and will be unique to your database. It could be a particular church baptism records, or a census year, or what your great aunt told you. Simply replace the number 16 with your source document code. Copy the example below to a notepad and save with a .sql extension, then load it into the SQL Query by clicking the "Load from file" icon under File on the SQL Query window.

--List Family/Person record, type, code, field name & notes for Source Document 16
SELECT S.RecordType, S.RecordCode, S.FieldName, S.Notes
FROM Sources S, Documents D
WHERE S.Document = D.Code AND D.Code = 16
ORDER BY S.RecordCode

The following variation adds the these extra tests:-

Only search family record types.
Look for the word "eureka" missing from the notes field.

The word "eureka" is case sensitive. You can change the word "eureka" for any other word or string, (you may test for "not shure about this" etc), that you expect to find in your notes. Remove the NOT before the LIKE to find notes fields that do contain the word "eureka".

SELECT S.RecordType, S.RecordCode, S.FieldName, S.Notes
FROM Sources S, Documents D
WHERE S.Notes NOT LIKE '%eureka%' AND S.RecordType LIKE '%F%' AND S.Document = D.Code AND D.Code = 16
ORDER BY S.RecordCode

Hope you find this useful,
John Wood

[SteveP]

Thanks John
This set me thinking.
Using separate queries for the Family and Person tables means you can include fields from those tables in the output (and if you wish the selection criteria too), making the resulting output more useful.

SELECT S.RecordType, S.RecordCode, F.Description, S.FieldName, D.Code, D.Title
FROM Sources S, Documents D, Families F
WHERE S.Document = D.Code AND S.RecordType = 'F' AND F.Code = S.RecordCode AND UPPER(D.Title) LIKE '%LETTER%'
ORDER BY S.RecordCode

The above includes the Family description and lists all records which link to a source containing the word 'letter' (or 'Letter' or 'LETTER').

Similarly instead of referencing the Sources table you can link to Notes.
I add a note with the description TO DO to every record where some information is missing, describing the work to do. The following then lists those records.
SELECT Notes.RecordType, Notes.RecordCode, People.Description Person, Notes.Description,
Notes.PrivacyLevel, Notes.Notes
FROM Notes, People
WHERE Notes.RecordType = 'P' AND People.Code = Notes.RecordCode AND UPPER(Notes.Description) LIKE '%TO DO%'

The column for People.Description is given the header "Person"
Using UPPER term guards against me entering "To Do" sometimes!

You could also search for text in the Notes.Notes field. I use a note ADDRESSES to store addresses on the Family table, so could include WHERE UPPER(Notes.Notes) LIKE '%LONDON%' to find all families who had ever lived in London.

These indicate the power of the SQL tool and really set the product apart from the likes of Ancestry - many thanks Nick!

Hope this helps someone

Steve Pritchet