Namo Web Editor

When you create a database-driven output blockA database-driven content area that displays information from one or more database records. involving a database that has more than one table, often the information you want to retrieve is spread across two or more tables. In such cases, you must specify a join condition for the block that describes how records in each of the tables match up.

For example, say you have a database table called Orders, in which each record contains information about an order placed by one of your customers for some product you make. You want to create a table block to display these orders in tabular form on a Web page.

If your Orders database table stands alone, having no relationship with other tables in the database, then you only need to use fields in the Orders table in the table block. Presumably, the Orders database table will have fields for order date, customer name, product name, quantity, and so forth; and this is exactly what you want to be displayed in the table block.

Now, suppose you have a more sophisticated database, in which the Orders table has a Customer field that contains not names but ID numbers. Another table in the database—call it Customers—contains names, addresses, phone numbers, and so forth for each customer ID. In this case, if you were to use only fields from the Orders table in your table block, the table block would display customer ID numbers, not names. To make the table block display customer names, you need to change the output elementAn element in an output block that displays the contents of one database field. linked to the Orders.Customer database field so that it links to the Customers.Name field instead.

However, simply changing the output element to use Customers.Name is not enough. Without additional information, the server would not know how records in the Customers table match up with records in the Orders table, and thus would not know which customer name to retrieve for a given order record. A join condition provides this information.

A join condition specifies the relevant relationship between two database tables. It consists of the names of two fields from two tables, with an equality sign in the middle:

  Table_1.Field_1 = Table_2.Field_2  

For our example scenario, the appropriate join condition would be:

  Orders.Customer = Customers.ID  

This statement tells the server: “For each customer ID in the Orders table, retrieve the customer name from the record in the Customers table that has the same ID number.”

Join conditions can have multiple equations separated by “AND”. Multiple equations are necessary when an output block uses more than two database tables. To continue our example, if the Orders database contains both customers and products as ID numbers, and you want the table block to display product names as well as customer names, you would need to use the Name field from the Products table and modify the join condition as follows:

  Orders.Customer = Customers.ID AND Orders.Product = Products.ID  

This statement tells the server: “For each customer ID in the Orders table, retrieve the customer name from the record in the Customers table that has the same ID number; and for each product ID in the Orders table, retrieve the product name from the record in the Products table that has the same ID number.”

In the Database Fields step of the Database Wizard, if you are using fields from multiple database tables, you need to include not only the fields that will be displayed in the output block, but also all the fields that are involved in the join condition. Thus, in our example, the following database fields would be included.

From the Orders table:

• Orders.Date
• Orders.Customer
• Orders.Product
• Orders.Quantity

From the Customers table:

• Customers.ID
• Customers.Name

From the Products table:

• Products.ID
• Products.Name

1. Start the Database Wizard for the desired type of output block and complete steps 1 and 2.
2. In step 3 of the wizard, Database Fields, add all the fields that match the following criteria:
   • Database fields for which you want output elements in the output block
   • Database fields that are involved in relationships between the tables included in the output block
3. In step 4 of the wizard, Selection Statement, enter the appropriate join condition in the Join box. (Clicking the … button next to the Join box reveals a menu of available database fields and operators; you can select one instead of typing it in manually.)
4. 
5. In step 5 of the wizard, Block Elements, remove the output elements corresponding to database fields you do not want to be displayed in the output block. (For example, you may wish to remove output elements corresponding to numeric ID fields in the database.)
6. Complete the remainder of the Database Wizard.

1. Right-click anywhere inside the block, point to Database, and then click Block Properties.
2. Click the Database Fields tab.
3. Add all the fields that match the following criteria:
   • Database fields for which you want output elements in the output block
   • Database fields that are involved in relationships between the tables included in the output block
4. Click the Selection Statement tab.
5. In the Join box, enter the appropriate join condition relating the tables included in the output block. (Clicking the … button next to the Join box reveals a menu of available database fields and operators; you can select one instead of typing it in manually.)
6. 
7. Click OK.
8. Double-click the placeholder for an output element that you want to change to link to a different database field.
9. Click the … button next to the Database field box and select the item corresponding to the desired database field. (For example, if the desired database field is Customers.Name, select <f,Customers.Name>.
10. Click OK.
11. Repeat steps 7 through 9 for each output element you want to change.