Archaeological Computing: Table Joins

Archaeological Computing

Table Joins and SQL Queries

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.




	Information Dissemination: • The CSA Newsletter Now in its twenty-third year and with all issues available on the Web, The CSA Newsletter includes articles about projects, specific technologies, and technology trends of interest. (A limited subject index is available. • Technology Information The CSA Technology Information web page is a gateway to materials designed to aid archaeologists and architectural historians when considering computer technologies, especially CAD and database management systems. • CSA Archival Work Many documents were created for CSA's digital archiving efforts. Those archival efforts have all been discontinued, but the documents concerning archival needs and procedures remain valuable. They may be found via the archives page. Projects: • The CSA Propylaea Project. This was a cooperative project to create a single digital resource of information about the Propylaea. The project has been terminated, and all materials produced may be found at the web site. • The Older Propylon Project. This is a concluded research project of CSA Director Harrison Eiteljorg, II. The results of this project have been published; archival materials, including CAD models, may be accessed through the Archaeological Research Institute at Arizona State University. • Lantern Slides of Classical Antiquity This project was a joint CSA/Bryn Mawr College project. Lantern slides from the College collection were digitized and made available on the Web, and high-resolution images were archived for future use. This project is now maintained by Bryn Mawr College. • Pompeii Forum Project CSA participates in this on-going project, Directed by Prof. John J. Dobbins (University of Virginia), to study the Forum of ancient Pompeii. A computer-aided design model is being constructed of the forum. • Bryn Mawr Electronic Resources Review (BMERR). BMERR was an online journal for reviews of electronic resources concerning the ancient world. Bryn Mawr Classical Review (BMCR) is now publishing reviews of electronic resources, and BMERR has discontinued its separate existence. Reviews published 1998-2000 are now available through the BMCR web site (http://bmcr.brynmawr.edu/) and they are available via the BMERR page there. • The CSA Guide to Archaeological Projects was a database of current archaeological projects maintained by CSA. Interest in participation was inadequate to justify continuing this project, and the information slowly grew out of date as well. As a result, the Web pages have been removed. Search this site. About this document.		Archaeological Computing Table Joins and SQL Queries 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 Author: Harrison Eiteljorg, II and the staff of CSA, Box 60, Bryn Mawr, PA 19010 (email: user-name nicke at (@) domain-name csanet.org; tel.: 484-612-5862) File name: joins.html Revision history: This document was originally posted on 12 April 2007. The current version is the second one and the first major change. Additional changes are expected to occur regularly; there will be no archiving of past versions, absent a specific need to do so. Posted 18 December 2008. Updated (only for slight changes in appearance of header) 18 March 2010. Internet access: This document is maintained at 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 the document was accessed.