Open Source Web Development with LAMP Using Linux, Apache, MySQL, Perl, and PHP [Electronic resources] نسخه متنی

5.4 Table Joins

In the world of relational databases, data often has complex relationships and is spread across multiple tables. Sometimes it is necessary to grab information from one table based on information in another. This requires that the two tables be JOINed.

For an example, we create a new table in the people database called addresses that contains information about people's addresses (surprise!). First, it must be created as follows:

mysql> CREATE TABLE addresses ( 
->     lastname CHAR(20), 
->     firstname CHAR(20), 
->     address CHAR(40), 
->     city CHAR(20), 
->     state CHAR(2), 
->     zip CHAR(10) 
-> ); 

The table needs some data:

mysql> INSERT INTO addresses 
->    (lastname, firstname, address, city, state, zip) 
->    VALUES ("Wall", "Larry", "Number 1 Perl Way", 
->            "Cupertino", "CA", "95015-0189" 
-> ); 
mysql> INSERT INTO addresses 
->    (lastname, firstname, address, city, state, zip) 
->    VALUES ("Torvalds", "Linus", "123 Main St.", 
->            "San Francisco", "CA", "94109-1234" 
-> ); 
mysql> INSERT INTO addresses 
->    (lastname, firstname, address, city, state, zip) 
->    VALUES ("Raymond", "Eric", "987 Oak St.", 
->            "Chicago", "IL", "60601-4510" 
-> ); 
mysql> INSERT INTO addresses 
->    (lastname, firstname, address, city, state, zip) 
->    VALUES ("Kedzierski", "John", "3492 W. 75th St.", 
->            "New York", "NY", "10010-1010" 
-> ); 
mysql> INSERT INTO addresses 
->    (lastname, firstname, address, city, state, zip) 
->    VALUES ("Ballard", "Ron", "4924 Chicago Ave.", 
->            "Evanston", "IL", "60202-0440" 
-> ); 

To verify the tables were populated, do this:

mysql> SELECT * FROM age_information; 
+------------+-----------+------+ 
| lastname   | firstname |  age | 
+------------+-----------+------+ 
| Wall       | Larry     |   46 | 
| Torvalds   | Linus     |   31 | 
| Raymond    | Eric      |   40 | 
| Kedzierski | John      |   23 | 
| Ballard    | Ron       |   31 | 
+------------+-----------+------+ 
5 rows in set (0.00 sec) 
mysql> SELECT * FROM addresses; 
+----------+---------+-----------------+--------------+-----+----------+ 
| lastname |firstname|address          |city          |state|zip       | 
+----------+---------+-----------------+--------------+-----+----------+ 
|Wall      |Larry    |# 1 Perl Way     |Cupertino     |CA   |95015-0189| 
|Torvalds  |Linus    |123 Main St.     |San Francisco |CA   |94109-1234| 
|Raymond   |Eric     |987 Oak St.      |Chicago       |IL   |60601-4510| 
|Kedzierski|John     |3492 W. 75th St. |New York      |NY   |10010-1010| 
|Ballard   |Ron      |4924 Chicago Ave.|Evanston      |IL   |60202-0440| 
+----------+---------+-----------------+--------------+-----+----------+ 
5 rows in set (0.00 sec) 

Now, on to the JOINs. Let's say we want to find out what city our under-40-year-old people live in. This requires looking up information in two tables: To find out who is under 40, we look in age_information, and to find out the city, we look in addresses. Therefore, we need to tell the SELECT command about both tables.

Because both tables are being used, we need to be specific about which table a particular field belongs to. In other words, instead of saying SELECT city, we need to say what table that field is in, so we say SELECT addresses. city. The addresses.city tells MySQL that the table is addresses and the field is city.

Moreover, we need to hook the two tables together somehowwe do so with the following command by making sure the lastname from the addresses row matches the lastname from the age_information row. Ditto for the firstname. So, our command is:

mysql> SELECT addresses.city 
->    FROM addresses, age_information 
->    WHERE age_information.age < 40 AND 
->          addresses.lastname = age_information.lastname 
->      AND addresses.firstname = age_information.firstname; 
+---------------+ 
| city          | 
+---------------+ 
| San Francisco | 
| NewYork       | 
| Evanston      | 
+---------------+ 
3 rows in set (0.02 sec) 

In English, we are saying, "give me the city for all the people with ages less than 40, where the last names and first names match in each row."

Let's grab the last names and zip codes for all those 40 and over, and order the data based on the last name:

mysql> SELECT addresses.lastname, addresses.zip 
->     FROM addresses, age_information 
->     WHERE age_information.age >= 40 AND 
->           addresses.lastname = age_information.lastname AND 
->           addresses.firstname = age_information.firstname 
->     ORDER BY addresses.lastname; 
+----------+------------+ 
| lastname | zip        | 
+----------+------------+ 
| Raymond  | 60601-4510 | 
| Wall     | 95015-0189 | 
+----------+------------+ 
2 rows in set (0.02 sec) 

As you can see, there are lots of different ways to query more than one table to get the exact information desired.