WebReference.com - Chapter 4 from The Joy of Dreamweaver MX, from Osborne/McGraw-Hill (7/8)
The Joy of Dreamweaver MX: Chapter 4
Using SQL Builder
Although Dreamweaver's advanced Recordset dialog box provides some options for
SELECT statements, it's not much use when it comes to
creating queries that use joins. To simplify the creation of an advanced query,
we'll use the SQL Builder tool included with HomeSite+.
In HomeSite+, select the Edit tab and choose
Tools | SQL Builder. This opens the Select Database Query dialog box. Choose
your RDS server from the drop-down menu and expand the connGlobal data source.
LOCATIONS table and click New Query.
The interface of SQL Builder is similar to Query Design view in Microsoft Access, and Query Designer in SQL Server. The three panes, from top to bottom, are the Table pane, the Properties pane, and the SQL pane.
Since we selected the
LOCATIONS table in the first dialog box, it already
appears in the Table pane. To add the
REGION table, click the Add Tables icon
on the SQL Builder toolbar. Select
and click Add. Click Done to close the Add Tables or Views dialog box. (Alternatively,
you can right-click inside the Table pane and choose Add Table | REGION.)
As you may recall from Chapter 3, the
tables are related by matching columns (
REGION.ID is the primary key and
LOCATIONS.REGION_ID is the foreign key). To create the join--in this case,
an equijoin--select the
ID column in the
REGION table and drag
and drop it onto the
REGION_ID column in the
A join line appears between the two tables. If you mouse over the join line, you'll see
a tooltip indicating that
REGION.ID = LOCATIONS.REGION_ID.
To select the data returned by the query, double-click
the following columns in the Table pane, or use the drop-down menus in the
The SQL pane updates to reflect each selection.
Click the Copy SQL To Clipboard icon on the SQL Builder toolbar. To exit SQL Builder, click the red "X" and click No when prompted to save and insert the new query. Click Cancel to dismiss the Select Database Query dialog box.
This returns you to
Chapter4.cfm in HomeSite+. Highlight the SQL
SELECT statement on line 2--
SELECT * FROM LOCATIONS--and press
CTRL-V to paste the query we just copied to the clipboard.
SELECT LOCATIONS.LOCATION_NAME,LOCATIONS.ADDRESS, LOCATIONS.CITY,LOCATIONS.STATE_COUNTRY,REGION.NAME FROM LOCATIONS,REGION WHERE REGION.ID=LOCATIONS.REGION_ID
Press CTRL-S to save
and exit HomeSite+. In Dreamweaver MX, click Yes when prompted to reload
the file. Open the Bindings panel and double-click the
recordset. The advanced Recordset dialog box opens because the query cannot be represented
in Simple mode.
Click Test. Instead of numbers, the query returns geographical locations. Click OK to close the Test SQL Statement dialog box. Click OK again to close the Recordset dialog box.
Congratulations! You did it. Now you can take advantage of SQL Builder in HomeSite+ to create advanced queries.
The Joy of Dreamweaver MX, By Paul Newman
Copyright (c) 2002 by The McGraw-Hill Companies
Created: September 16, 2002
Revised: September 16, 2002