This page is intended to augment discussion in Archaeological Computing about table joins and SQL statements. It assumes that you have read the sections about table joins and select statements in the book.

These tables and the data organization described in Archaeological Computing will be used for this discussion:

Consider these joins plus SQL statements (as expressed in English):

Sample 1: Produce a list of the references linked to objects in the exhibition.

• Use an inner join to combine the Pots Table and References Table (using Object No. in each table), creating a new table with some columns from Pots and at least Source No. from References. Put these columns into a new table called Temp1.
• Now use another inner join to combine the Temp1 and the Sources Table (using Source No. in each table), creating a new table with some columns from Temp1 and some from Sources. Put these columns into a new table called Temp2.
• Now select all rows and the desired columns fromTemp2 to print a list of references related to exhibit objects.
• Your list will be only of those references used for the objects in the exhibition. Any sources used only for comparanda will not be included because of the first inner join that yielded a table with only references linked to the exhibit items.

Sample 2: Select those Moche periods for which there are both exhibit objects and specified comparanda.

• Use an inner join to combine the Pots and Comparisons Table (using Object No. in each table), creating a new table with at least the columns Object No. and Period No. from Pots and some columns from Comparisons. Put these columns into a new table called Temp1.
• Now use another inner join to combine Temp1 and the Periods Table (using Period No. in each table), creating a new table with columns from Temp1 and Periods. Put these columns into a new table called Temp2.
• Temp2 has columns from the Periods table only for the objects in the exhibit that have comparanda. The two inner joins combined to eliminate pots without comparanda and thus periods without comparanda. Is this useful? It certainly is if the exhibition curator wants to be sure that all the periods represented/discussed in the exhibit have both objects and explicit comparanda.

Sample 3: Produce a list of references used to refer to or illustrate comparanda.

• Use an inner join to combine the Comparanda Table and the Sources Table (using Source No. in each table), creating a new table with at least some columns from each table. Put these columns into a new table called Temp1.
• The result is a simple list of sources used for comparanda whether in text or illustrations.
• If we had wanted only those sources with illustrations, we could have begun by making a new table from the Comparanda Table and selecting only the entries with illustrations. The new table could have been joined (inner join) to the Sources Table.

Sample 4: Provide a list of all objects in the exhibit that have no comparanda.

• Start with a left outer join of the Pots Table (the left table here) and the Comparisons Table to make a new table with all objects in the exhibit, some of which will also have information about a comparison, call it Temp1. (Note that I have not specified the columns selected here.)
• A SQL statement selecting all the objects with no information in a column taken from the Comparisons Table would provide a list of all the objects in the exhibit without comparanda.
• The outer join to begin the process created a new table with all pots but, for pots without comparanda, no information in the columns from the Comparanda Table; so the rows with null columns identify the pots with no comparanda.

 

---

---

 

About this document:

• Title: Archaeological Computing - Table Joins and SQL Queries
• File name: joins.html
• Author: Harrison Eiteljorg, II and the staff of CSA, Box 60, Bryn Mawr, PA 19010, (email: see the CSA email contacts page.) tel.: 610-256-4665)
• Revision history: This document is now in its initial form, but changes are expected to occur regularly; there will be no archiving of past versions. Posted 2 April 2007.
• Internet access: This document is maintained at archcomp.csanet.org by the Center for the Study of Architecture and Harrison Eiteljorg, II. Note that there may be changes in computer addresses that are beyond the control of CSA.
• Long-term availability: This document or its successors will be maintained for electronic access indefinitely. Prior versions will not be archived.
• Citation permissions and copyright information: This document is copyrighted by CSA. Citations should include the date of the document cited.