HOW TO: Query Two Tables in the Same Database in FrontPage 2002

This step-by-step article describes how to retrieve information from two tables in a database. The example case uses SQL statements to find data in both tables that matches a submitted value for a column that is used in both tables.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Create Query with Two Tables

To set up using a search form to bring up specific values from two tables, follow these steps:

1. Create two pages, with the titles Search.asp and Results.asp.
2. Open the Search.asp page.
3. On the Insert menu, point to Form, and then click Textbox.
4. Right-click the text box, and then click Form Field Properties on the shortcut menu.
5. In the Name box, type ID, and then click OK.
6. Right-click the text box, and then click Form Properties on the shortcut menu.
7. Click to select the Send to other check box, click Options, and then type results.asp in the Action box. Click OK twice.
8. On the File menu, click Save.
9. Click Results.asp.
10. On the Insert menu, point to Database, and then click Results.
11. Click to select the Use a sample database connection check box, and then click Next.
12. Click to select the Custom query check box, and then click Edit.
13. In the SQL Statement box, type or paste the following text:
    Select * From Categories,Products Where Categories.CategoryID=::ID:: and Categories.CategoryID=Products.CategoryID
    NOTE: This returns all the columns from the Categories and Products table where the ID sent from the Search.asp page matches the CategoryID in the Categories table and the Category ID in the Categories table matches the CategoryID in the Products table.
14. Click OK.
15. Click Next three times.
16. Click to select the Display all records together check box, and then click to clear the Add search form check box.
17. Click Finish.
18. On the File menu, click Save.
19. Open the Search.asp page.
20. On the File menu, click Preview in Browser, and then click OK twice.
21. In the text box, type 3, and then click Submit.

This returns all the columns from Categories and Products with a CategoryID of 3. If any valid ID is submitted, the correct records are returned.

REFERENCES