Looking Up Foreign Table Data with SQL

This page is coming together, but you still haven't resolved the issue of the World Region being displayed as a number, rather than as the name of a continent. In this task, you will solve this problem with some fancy SQL.

Let's revisit the problem, before attempting to solve it. Relationships are the cornerstone of most modern database management systems, such as Microsoft Access. They enable people to organize their data in the most efficient format possible, by eliminating redundancy and simplifying data maintenance. Again, the way they work is that database developers put the unique key from a given table into another table as a foreign key. By doing so, developers can use a query to assemble all information from both tables, correlated to any given record. In tbl_country, there is a field for region, and its value is the unique key taken from tbl_region's unique regionID field.

Let's be more concrete. In tbl_region, the record that contains North America has a unique key of 1. The record that contains East Asia has a unique key of 4. In tbl_country, the records that contain Canada and the United States both have world regions of 1, which means both are in North America. Japan, Thailand, and Taiwan all have world regions of 4, meaning they are in East Asia. This relationship is shown in the following screenshot.

graphics/10fig30.gif

The catch, as you know, is that the only actual data from the region table is this unique key, and that is not meaningful to regular users. What you need to do is combine the data from two tables (tbl_country and tbl_region) where they intersect. Thus, if the user selects Canada, not only will the query retrieve all of the data from the Canada row in tbl_country, but it will also retrieve the data associated with the region value (1) in that tableand in the case of Canada, that data is North America.

Unfortunately, Dreamweaver's SQL builder is meant for relatively simple queries, rather than queries that combine data from two tables using a relationship. That means that you'll have to code some of the SQL by hand. Dreamweaver has an advanced SQL window that lets you hand-code SQL, but let's edit it directly in the document, so you can get some experience working with SQL.

In profiles_detail.asp, switch to code view. Find the SQL statement as it appears in the code, using the code listings below.

In ASP:
```
[View full width]
SELECT * FROM tbl_country WHERE countryID = " + Replace(rs_countries__MMColParam, "'", ",,
") + ""
```
In ColdFusion:
```
SELECT * FROM tbl_country WHERE countryID = #URL.countryID#
```
Paraphrased, both of these statements say the same thing: Retrieve every field from tbl_country where the value in a given record's countryID field equals the countryID passed as a URL parameter. As mentioned earlier, to prevent errors from occurring if the page is loaded and there is no URL parameter, Dreamweaver has added a default value (1). This value is stored in ASP using the MMColParam variable, and in ColdFusion, it is stored in URL.countryID.

This is all good so farthere's no data retrieved that we don't want. It's just that we need a little more.

NOTE

Though ColdFusion users will find that their code varies from the ASP code shown in the screenshot, the steps still work as described.
Replace tbl_country with tbl_country, tbl_region, being careful to leave the remaining code intact.

Originally, the query was retrieving all of the fields from tbl_country. By making this change, you are telling it to retrieve all of the fields from both tbl_country and tbl_region.
Place the insertion point after WHERE, and insert the following code, being careful not to change the subsequent code.
```
tbl_region.regionID = tbl_country.region AND
```
This code creates a new criterion that retrieves only the record(s) from tbl_region that correspond to records that have been retrieved from tbl_country. In other words, if only Canada is retrieved from tbl_country, then only the record that has the same regionID (in the case of Canada, 1) will be retrieved from tbl_region.

The extra AND tacked onto the end means that both this criterion and the original one, which specified that only records corresponding to the URL parameter should be retrieved, must be met.

Speaking more concretely, this revised query will add two new fields to the recordset: regionID and regionName from tbl_regions. In addition, when it does, these will be correlated so that only the regionID and regionName for the active country are retrieved.
In the Bindings panel, click the Refresh button to verify that regionID and regionName are appended to the list.

Even though Dreamweaver's Recordset dialog doesn't let you build this code from a wizard, Dreamweaver understands it.
In design view, delete the rs_countries.region dynamic text just beneath World Region, and in its place, insert the regionName field from the Bindings panel.

In this step, you are outputting the region name itself. You are confident that it will output the right region (that is, it won't say Canada is in East Asia) because of the SQL code you just wrote.
Save, upload, and test the file.

You now see the country's continent, rather than an arbitrary number in the World Region section.