Kith and Kin Pro manual

Here is a list of the most frequently used SQL clauses, operators and reserved words which apply to queries used in the Search Window. This is not a complete list but should be sufficient for any queries in Kith and Kin Pro. For a complete introduction to ANSI-standard SQL, see one of the many Web sites or third-party books.

Every SQL statement must begin with SELECT and then a list of field names or "*" for all field names. Field names can be qualified by being prefixed by the table name that they belong to and a dot, eg. People.Code, Families.Code.

A complete SQL statement has a SELECT line first followed by a FROM clause line and then any additional clause lines.

Clauses

SELECT	specifies fields to include, eg. Surname, Birth date
FROM	specifies the tables to include, eg. People, Sources
WHERE	specifies the conditions used to include records
ORDER BY	specifies any fields used for sorting

Operators

Arithmetic	+ - * /
Comparison	< > = <> >= =< IS NULL IS NOT NULL
Logical	AND OR NOT
String	% (wild card) _ (character replacement)

Reserved words

UPPER()	forces string to upper case
LOWER()	forces string to lower case
LIKE	non-exact string matches
IN()	single value from list
JDATE()	converts a date into a julian date - must be used for date comparisons

Examples

Selects all the fields and all the records from the People table.

SELECT *
FROM People

Selects only the "Description" and "Birth date" fields but all the records from the People table.

SELECT Description, Birth date
FROM People

Selects only the "Description" and "Birth date" fields but all the records from the People table. These records are then sorted by "Birth date"

SELECT Description, Birth date
FROM People
ORDER BY Birth date

Selects only the "Description" and "Birth date" fields from the People table. Only records which have an entry in "Birth date" are selected. These records are then sorted by "Birth date"

SELECT Description, Birth date
FROM People
WHERE Birth date IS NOT NULL
ORDER BY Birth date

Selects only the "Description" and "Birth date" fields from the People table. Only records which have a "Birth date" on or after 1 JAN 1900 are selected. The "Description" field is given the heading "Person" in the results list. The "JDATE" function converts a date string to a julian date so that it can be compared with the "Birth date" field. "JDATE" uses the input date format set in Preferences.

SELECT Description Person, Birth date
FROM People
WHERE Birth date >= JDATE('? ? 1900')

Selects the "Firstnames" field from the Person table. Only records which have "fred" in the "Firstnames" filed are selected, eg "Fred", "Alfred". The "LOWER" function forces each string to lower case thus making the comparison case-insensitive. The "LIKE" function allows you to do partial matches on strings; the "%"s indicate that it does not matter what text is before or after "fred".

SELECT Firstnames
FROM People
WHERE LOWER(Firstnames) LIKE LOWER('%fred%')

Selects only the "Description" and "Male" fields from the People table. Only records which have something in the "Surname" field and "Male" is true are selected. Note that fields can appear in the "WHERE" clause which are not in the "SELECT" line. This statement uses "AND" to select only records which match both "Surname <> ''" and "Male = True".

SELECT Description, Male
FROM People
WHERE Surname <> '' AND Male = True

Selects only the "Description" and "Birth date" fields from the People table. Only records which have people who were not born in the 1800s are selected. This statement uses "OR" to select any records which match either "Birth date < JDATE('? ? 1800')" or "Birth date > JDATE('31 Dec 1899')".

SELECT Description, Birth date
FROM People
WHERE Birth date < JDATE('? ? 1800') OR Birth date > JDATE('31 Dec 1899')

Selects only the "Description" field from the People table. Only records which have "Code"s of 18 or 20 are selected, ie. only person [P18] and person [P20].

SELECT Description
FROM People
WHERE Code IN(18,20)

This statement finds all the people who have pictures. Selects only the "Description" fields from both the Pictures table and the People table and joins the records together. Each row in the results list contains the person's description and the corresponding picture description. The ORDER BY clause makes sure that the pictures for a particular person are grouped together. Note that the tables have been given different names ("Prs" and "Pic") as have the "Description" fields ("Person" and "Picture"). The WHERE clause joins the tables together using Pic.RecordCode = Prs.Code. The RecordType = 'P' condition ensures that no 'F' (family) pictures are mistakenly assigned to people.

SELECT Prs.Description Person, Pic.Description Picture
FROM Pictures Pic, People Prs
WHERE Pic.RecordType = 'P' AND Pic.RecordCode = Prs.Code
ORDER BY Pic.RecordCode

Note that using upper-case for reserved words and lower-case for field names is merely a convention used to improve readability.

SQL syntax

Clauses

Operators

Reserved words

Examples

See also