• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Using SQL > Retrieving Data From Multiple Tables

Retrieving Data From Multiple Tables

So far in this chapter, we’ve dealt only with recordsets that get their data from a single table. Because most database applications need to use data from more than one table, the procedures outlined earlier are only the beginning.

Using the Database Items tree

Dreamweaver provides a tool to create recordsets from more than one table, called the Database Items tree. This allows you to pick fields from all the tables in your database without having to type too much.

To create a recordset using the Database Items tree:
In the Application panel group, click on the Data Bindings panel.

Click the + (plus) button. Select Recordset (Query) from the menu.

In the Recordset dialog box, name the recordset and select your connection.

You can ignore the table drop-down menu for the time being.

Click the Advanced button to switch to the advanced mode.

Select all the text in the SQL text box and delete it.

No, really, do this. You’ll be generating all-new text using the Database Items tree.

In the bottom panel of the dialog box, which is the Database Items tree, click on the Expand button (+) next to the word Tables to show all the tables in your database (Figure 4.20).

Figure 4.20. The Database Items tree includes a list of all the tables in your database.

Expand one of the tables to show a list of the fields in that table (Figure 4.21).

Figure 4.21. Expanding a table shows all of its fields.

Select the first field you want in your recordset and click the SELECT button.

The field will appear as part of the SELECT statement in the SQL window, and the table name will appear in the FROM clause.

Repeat Step 8 for each field you wish to show (Figure 4.22).

Figure 4.22. Clicking the SELECT button adds the highlighted field to the SQL command.

Select the field you want to use for the WHERE clause. Click the WHERE button to add that clause to the SQL window.

In the SQL window, after the field name, type the condition the WHERE clause requires (Figure 4.23).

Figure 4.23. You need to type the WHERE condition manually.

If you want to sort by a field, select it and click the ORDER BY button.

Your statement is now complete (Figure 4.24).

Figure 4.24. To sort the results by a particular field, we can add an ORDER BY statement.

Finally, click TEST to show the results of the statement you’ve built.

Some New SQL Details

When you click on a field name, you’ve probably already noticed it appears in the SQL window with the name of the table and a period before it. This is SQL’s way of making sure it knows exactly which field you are specifying. Several tables can have a field called ID, for example, so you need to specify customer.ID for the customer name and product.ID for the product name.

Additionally, you probably want to stay away from using the word “name” as a fieldname, because it’s a reserved word in Access databases. In Access, you can use fname, lname, or prod_name, or you’ll get a complicated error message when the page loads in the browser.

Selecting data from multiple tables

We could have executed all the previous tasks much more simply—you don’t always need to check to see how the SQL is working. But now that you have an idea of what’s happening behind the scenes, we can get to the real meat of working with SQL: joining data from more than one table.

In most cases, when you’re working with multiple tables, one field in a table corresponds to a field in another table. Let’s return to the product catalog example we used at the beginning of this chapter. There might be a second table, called purchases, which has a list of purchases with a product ID and the quantity purchased. (If this were an actual online store, we’d probably need to know when the purchase was made and by whom, but for our purposes, that’s unnecessary.) In this case, the product ID would be the field that combines the two tables.

To create a recordset from multiple tables:
Follow steps 1-6 from the previous task, To create a recordset using the Database Items tree.

Expand all the tables that have data you want to include (Figure 4.25).

Figure 4.25. You can expand more than one table.

Select all the fields you want to display by clicking on the field name and clicking the SELECT button.

Select the field in one table that corresponds to the field in the other table (Figure 4.26).

Figure 4.26. The product_ID field is the link between tables in our example.

In our example, the fields are both named product_ID. First we’ll select this field in the product_catalog table.

Click the WHERE button.

In the SQL window, type the equal sign (=) after the field name.

Select the corresponding field in the other table (Figure 4.27) and click the WHERE button.

Figure 4.27. A WHERE clause can include fields from more than one table.

In Step 4, we selected the field product_ID in the product_catalog table. Now we’re selecting the product_ID field in the purchases table.

Click Test to see the results of your query (Figure 4.28).

Figure 4.28. The test window shows two tables joined.


  • Generally, once you’ve done anything in the Advanced mode of the Recordset window, Dreamweaver will not let you switch back to the Simple panel. If you need to go back, you can delete everything from the SQL text box, and click the Simple button.

This chapter has shown you some of the basics of SQL and how to use Dreamweaver to get data out of a SQL database. In the next chapter, we will look at how you can use your data now that you have it.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint