| 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 productthe 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. 
|
