Database Reference
In-Depth Information
contact_name VARCHAR ( 25 ) NOT NULL , # name to use for contacting person
INDEX ( profile_id )
);
The table associates each row with the proper profile row via the profile_id column.
The service and contact_name columns name the media service and the name to use
for contacting the given person via that service. For the examples, assume that the table
contains these rows:
mysql> SELECT * FROM profile_contact ORDER BY profile_id, service;
+------------+----------+--------------+
| profile_id | service | contact_name |
+------------+----------+--------------+
| 1 | Facebook | user1-fbid |
| 1 | Twitter | user1-twtrid |
| 2 | Facebook | user2-msnid |
| 2 | LinkedIn | user2-lnkdid |
| 2 | Twitter | user2-fbrid |
| 4 | LinkedIn | user4-lnkdid |
+------------+----------+--------------+
A question that requires information from both tables is, “For each person in the
profile table, show me which services I can use to get in touch, and the contact name
for each service.” To answer this question, use a join. Select from both tables and match
rows by comparing the id column from the profile table with the profile_id column
from the profile_contact table:
mysql> SELECT id, name, service, contact_name
-> FROM profile INNER JOIN profile_contact ON id = profile_id;
+----+---------+----------+--------------+
| id | name | service | contact_name |
+----+---------+----------+--------------+
| 1 | Sybil | Twitter | user1-twtrid |
| 1 | Sybil | Facebook | user1-fbid |
| 2 | Nancy | Twitter | user2-fbrid |
| 2 | Nancy | Facebook | user2-msnid |
| 2 | Nancy | LinkedIn | user2-lnkdid |
| 4 | Lothair | LinkedIn | user4-lnkdid |
+----+---------+----------+--------------+
The FROM clause indicates the tables from which to select data, and the ON clause tells
MySQL which columns to use to find matches between the tables. In the result, rows
include the id and name columns from the profile table, and the service and con
tact_name columns from the profile_contact table.
Here's another question that requires both tables to answer: “List all the profile_con
tact records for Nancy.” To pull the proper rows from the profile_contact table, you
need Nancy's ID, which is stored in the profile table. To write the query without looking
up Nancy's ID yourself, use a subquery that, given her name, looks it up for you:
Search WWH ::




Custom Search