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 JOIN ed. 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 firstnameaddress city statezip +----------+---------+-----------------+--------------+-----+----------+ 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 KedzierskiJohn 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 JOIN s. 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 somehow ”we 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. |