The Basics of SQL Joins in MySQL, Page 2

The SELF JOIN

Believe it or not, it's also possible to join a table to itself. For instance, suppose your company instituted a customer referral program, providing customers with a cash incentive for inviting other organizations to do business with you. You'd naturally want to track referral histories, which you could do easily enough by adding a column named referrer_id to the customers table:

CREATE TABLE customers (
 -> id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 -> referrer_id INTEGER UNSIGNED NOT NULL,
 -> name VARCHAR(255) NOT NULL,
 -> profession_id INTEGER UNSIGNED NOT NULL
);

For each customer, the referrer_id cell will be set to either 0, meaning nobody referred the customer, or an integer representing another customer's primary key:

+----+-------------+-------------------------+---------------+
| id | referrer_id | name                    | profession_id |
+----+-------------+-------------------------+---------------+
|  1 |           0 | Acme, Inc.              |             1 | 
|  2 |           1 | Taylor Made Teapots     |             4 | 
|  3 |           0 | Robin's Egg Farm        |             1 | 
|  4 |           2 | Paul's Plumbing         |             3 | 
|  5 |           2 | Charlie's Chicken Coops |             5 | 
+----+-------------+-------------------------+---------------+

Using the SELF JOIN, you can create a list of customers and their referring counterpart:

SELECT c.name AS "Customer", r.name AS "Referred By" 
FROM customers c, customers r 
WHERE c.referrer_id = r.id;

Executing this SELF JOIN produces output similar to the following:

+-------------------------+---------------------+
| Customer                | Referred By         |
+-------------------------+---------------------+
| Taylor Made Teapots     | Acme, Inc.          | 
| Paul's Plumbing         | Taylor Made Teapots | 
| Charlie's Chicken Coops | Taylor Made Teapots | 
+-------------------------+---------------------+

Conclusion

Mastering JOINs will elevate your ability to effectively manage large datasets. Just like learning to ride a bicycle though, it takes some practice to get used to the unfamiliar syntax. However, after some time you'll wonder how you ever got along without this powerful feature!

About the Author

Jason Gilmore is founder of EasyPHPwebsites.com. He is the author of several popular books "Easy PHP websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Third Edition".

Page 2 of 2