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: