Dykes L. Macromedia Dreamweaver 8 Advanced for Windows and Macintosh. Visual Quickpro Guide / Filtering Data

Computers & Technology
Education & Reference
Business & Investing
Science & Math

Macromedia Dreamweaver 8 Advanced for Windows and Macintosh. Visual Quickpro Guide
Authors: Dykes L.
Published year: 2004
Pages: 49-50/129

Buy this book on amazon.com >>

<< Previous page
Table of contents
Next page >>

Filtering Data

You can extract a subset of the data from a SELECT statement by adding a WHERE clause. For example, if you want to retrieve data only on a certain set of members in the travel club, rather than all the members , you specify the criteria for that set by adding a WHERE clause to your SELECT statement.

In the following task, you'll select the first_name and last_name fields from the members table and then filter that data by selecting only those members whose id is less than a certain number. You'll then filter the data by selecting only those members whose id is within a certain range of numbers . Last, you'll add an IS NULL clause to a WHERE clause to check for missing data in the flight_time field in the flightprefs table.

To filter data using SQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
2.	At the `mysql` prompt, type `use travelclub;` and press Enter (Windows) or Return (Mac) to choose the travelclub database. The next step is to select the last_name and first_name fields in the members table, and then filter that data by selecting only those whose id value is less than 30000.
3.	At the prompt, type `SELECT last_name, first_name FROM members WHERE id < 30000;` and press Enter (Windows) or Return (Mac). The two rows and columns that meet these criteria (last_name, first_name, and id < 30000) appear ( Figure 5.17 ). Figure 5.17. Filtering a selection with the Less Than ( `<` ) operator. Next, you'll select the last_name and first_name fields in the members table, and then filter that data by selecting only those whose id value is between 20000 and 40000.
4.	At the prompt, type `SELECT last_name, first_name FROM members WHERE id BETWEEN 20000 AND 40000;` and press Enter (Windows) or Return (Mac). The two rows and columns that meet these criteria (last_name, first_name, and id between 20000 and 40000) appear ( Figure 5.18 ). Figure 5.18. Filtering a selection with the BETWEEN keyword. In the next step, you'll select the id field from the flightprefs table and then filter that data so that you can identify any records that don't have a value for flight_time.
5.	At the prompt, type `SELECT id FROM flightprefs WHERE flight_time IS NULL;` and press Enter (Windows) or Return (Mac) to check for missing data. The result is an empty set, which means that all the records in the flightprefs table include a value for the flight_time field, so there is no missing data in this field ( Figure 5.19 ). Figure 5.19. Checking for missing data with the IS NULL clause.
6.	At the prompt, do one of the following: If you're running Windows, type `exit` and press Enter to close the MySQL Monitor. If you're on a Mac, type `exit` and press Return to stop MySQL, type `exit` and press Return to log out, and then quit Terminal and close the Terminal window.

Tip

If you use a WHERE clause with an ORDER BY clause, make sure the ORDER BY clause is placed after the WHERE clause. Otherwise, an error will be generated.

Joining Tables

You can use SQL to create complex queries that select data from multiple tables in a database. A join operation combines tables so that it can extract related data. However, there must be a relationship between the tables for a join operation to work. The flightprefs and members tables have a relationship between their primary keys (both of them use id as their primary key), so we can combine data from both tables.

In the next task, you'll combine information from the members table (last_name, first_name) and the flightprefs table (flight_time, seat_location) by joining the two tables in a SELECT statement. In this way, you can extract a set of data that combines the members' names with their preferences for flight times and seat locations.

To join tables using SQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
2.	At the `mysql` prompt, type `use travelclub;` and press Enter (Windows) or Return (Mac) to choose the travelclub database. The next step involves creating a query that combines fields from the members table with fields from the flightprefs table by joining the two tables using a SELECT statement.
3.	At the prompt, type `SELECT last_name, first_name, seat_location, flight_time FROM members, flightprefs WHERE members.id = flightprefs.id;` and press Enter (Windows) or Return (Mac). It's usually not necessary to add a table identifier to a field name, but in this case there are two id fields, one in the flightprefs table and one in the members table, so we've added the table name to each id field with a period to separate the table name and the field name (members.id and flightprefs.id). The combined data from both tables for the specified fields appears ( Figure 5.20 ). Figure 5.20. Using a join operation to query two related tables. Now we need to filter the combined data from the two tables by adding `AND` to the WHERE clause and including a condition (id greater than 25000).
4.	At the prompt, type `SELECT last_name, first_name, seat_location, flight_time FROM members, flightprefs WHERE members.id = flight prefs.id AND members.id > 25000;` and then press Enter (Windows) or Return (Mac). The filtered and combined data from both tables appears ( Figure 5.21 ). Figure 5.21. Using a join operation and a filter to query two tables.
5.	At the prompt, do one of the following: If you're running Windows, type `exit` and press Enter to close the MySQL Monitor. If you're using a Mac, type `exit` and press Return to stop MySQL, and then type `exit` and press Return to log out, and then quit Terminal and close the Terminal window.

Tip

Make sure you use a WHERE clause when you create a query that includes a join operation. Otherwise, your data will include every row of results from the first table combined with every row of results from the second table ( Figure 5.22 ). If there are four rows in each table, your result will include sixteen rows (the number of rows in the first table multiplied by the number of rows in the second table). This is called a Cartesian product the number of rows in the first table multiplied by the number of rows in the second table. As you can see in Figure 5.22, this can produce erroneous results.

Figure 5.22. Using a join operation without a WHERE clause creates a Cartesian product, in which every row of results from the first table is combined with every row of results from the second table.