Database Guide

Selecting rows from multiple tables (join operation)

The query in the following example joins the STAFF and ORG tables of the SAMPLE database. The first block of code issues the SQL statement directly in the code. The second block retrieves the SQL statement from an access set. To look at the results of this query, follow these steps:

  1. Evaluate the code using the Inspect command.
  2. Look at the contents of the ordered collection in the Inspector window.
"Select rows from multiple tables"
| querySpec result resultCollection connection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SAMPLE'.
querySpec := (AbtQuerySpec new)
     statement: 'SELECT ORG.DEPTNAME, ORG.DIVISION, ORG.LOCATION,
                     ORG.MANAGER, STAFF.DEPT, ORG.DEPTNUMB,
                     STAFF.NAME FROM ORG, STAFF WHERE
                     (ORG.DEPTNUMB = STAFF.DEPT)'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow asString)].
^resultCollection.
 
"Select a row from multiple tables using an access set"
| result resultCollection connection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SAMPLE'.
result := connection resultTableFromQuerySpec:
     (AccessSetName getQuerySpecNamed: #Join).
result do: [:eachRow | resultCollection add: (eachRow asString)].
^resultCollection.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]