HOWTO: Joining Tables

General Overview

With mySiteMaker you can join data from multiple tables in such a way that that you can create queries that both search and sort on columns in many different tables and the results will show data from different tables. Below is a description of how mySiteMaker deals with different types of table relationships and how to add these to your mySite.

Two Types of Relations

mySiteMaker lumps the different types of possible relations into two groups: direct relations and indirect relations.

You need a corresponding INPUT_OPTIONS entry for each direct and indirect relation tuple (we're calling each direct relation "x=y" thingy and each input option "x=y=z" thingy a 'tuple'). See CONF_MODS-HOWTO.html for more information on the INPUT_OPTIONS directive.

Direct Relations

Relations that do not require an intermediate table in order for the relation to function are called Direct Relations. This includes one-to-one and many-to-one relations where all that is needed for the relation to work between two tables is that one table store a foreign key to the entries in the other table. mySiteMaker will allow you to join tables via direct relations.

Figure 1: A Direct Relation.

Table 1
name	color_id
Jack	2
Diane	3
Anne	1
Dave	1
Stacy	2
Paul	3

Table 2
id	color
1	Red
2	Green
3	Blue

Results
1.name	2.color
Jack	Green
Diane	Blue
Anne	Red
Dave	Red
Stacy	Green
Paul	Blue

color_id, in Table 1, is matched with id, in Table 2, and replaced with the corresponding color string. Notice that in this scheme, each name can only be associated with one color.

Joining Tables With Direct Relations

Using the text editor of your choice, open the configuration file for the mySite that you wish to modify. Configuration files can be found in a directory named "conf" that is located where the mySiteMaker CGIs are stored. Configuration files are named after the database and table that they are created for. For example: If you created a mySite for a table named "addresses" in a database named "personnel", then the configuration file for this site would have the name "personal_addresses.conf".
Add the new column names to the list of existing column names following the COLUMN_NAMES parameter. Be sure to put a colon (':') character between each column name and be sure to proceed the column name with the name of the table that the column is in, with a dot ('.') between the table name and the column name.
For example: For a column named "name" from a table named "people", add ":people.name" to end of the list.
If it does not already exist, add a new line with the parameter DIRECT_RELATIONS. The parameter name should then be followed by a white space character.
After the white space, add values (described below) to DIRECT_RELATIONS. If DIRECT_RELATIONS already exists, simply add a colon character (':') to the end of the existing list and then add the new values. Enter these values, separated by an equals ('=') sign:
1. The name of the column (in the format: tablename.columnname - note the dot between tablename and columnname) in the main table that holds an identifier that is used to match an identifier in the other table. For Example: In the illustration for direct relations, Figure 1, this column name would be "1.color_id".
2. The name of the column (in the format: tablename.columnname) in the other table that holds an identifier that is used to match the column in the main table. For example: In the illustration for direct relations, Figure 1, this column name would be "2.id".
Thus, for Figure 1, we would end up with the following lines:
```
DIRECT_RELATIONS 1.color_id=2.id
INPUT_OPTIONS 1.color_id=2.color=pulldown
```
("pulldown" could also be "radio")
If you wish to allow people to search and sort on these columns, then you must modify the HTML search page. You can find the HTML search file in the location that you are storing mySiteMaker HTML files. Search files are named after the database and table name that the mySite was create for. For example, if you created a mySite for the table, "address" in the database, "personnel", then the HTML search page would be named "personnel_address_search.html".
Once you open the appropriate HTML file, simply add new <option> tags to the "column", "order_column" and, if using the file upload feature, the "upload_column" pulldown menus.
Thus, for Figure 1, we would add the following tag:
```
<OPTION VALUE="1.color">1.color
```
to the "column", "order_column" and if present, "upload_column", pulldown menus.
All Done!!!

Indirect Relations

Relations that do require an intermediate table in order for the relation to function are called Indirect Relations. Many-to-many relations require intermediate tables in order to function correctly. Indirect relations are poorly implemented. You may not be able to search, sort, or query on them. Try it, and if it doesn't work file a bug report.

Figure 2: An Indirect Relation.

Table 1
id	name
1	Jack
2	Diane
3	Anne

Intermediate Table
1.id	2.id
1	2
1	3
2	1
3	1
3	3

Table 2
id	Color
1	Red
2	Green
3	Blue

Results
1.name	2.color
Jack	Green, Blue
Diane	Red
Anne	Red, Blue

The values for id, from both Table 1 and Table 2, are stored in an intermediate table that can be joined in such a way as to allow each name to be associated with multiple colors.

Joining tables with Indirect relations

The whole indirect relations thing is shaky and buggy and poorly supported. If you do find bugs, please report them at the web site (see bottom of this page).

Open the conf file
Add the new column names to the list of existing column names following the COLUMN_NAMES parameter. Be sure to put a colon (':') character between each column name and be sure to proceed the column name with the name of the table that the column is in, with a dot ('.') between the table name and the column name.
If it does not already exist, add a new line with the parameter INDIRECT_RELATIONS. The parameter name should then be followed by a white space character.
After the white space, add values (described below) to INDIRECT_RELATIONS. If INDIRECT_RELATIONS already exists, simply add a semicolon character to the end of the existing list and then add the new values. Enter these values, separated by an equals ('=') sign. I think the column names should not be qualified with a table name (don't do table.column, just say column)
- r_table: the table in the relation that holds the data we are interested in joining into the results from our query to the main table.
- int_table: the intermediate table that simply holds key values from the two tables that hold interesting data that we want to join
- main_key: a column in the main table that holds key values. In this case the key values are the primary key values for the main table
- int_main_key: the column in int_table that holds values from main_key
- r_key: the name of r_table's primary key column
- int_r_key: the column in int_table that holds values from r_key
Note that there are 3 tables involved: a lookup table (r_table), your main table (DB_TABLE), and an intermediate table. The link is like this:
```
r_table.r_key=int_table.int_r_key and DB_TABLE.main_key=int_table.int_main_key
```
at least in theory.
Searching and sorting on INDIRECT_RELATION columns should work. Bugs should be reported (see below)