Understanding SQL Joins - Up A Tree (
Page 9 of 10 )
In addition to inner and outer joins, SQL also allows a third type of join, known as a "self join". Typically, this type of join is used to extract data from a table whose records contain internal links to each other. Consider the following table, which illustrates what I mean:
+----+------------------------+--------+
| id | label | parent |
+----+------------------------+--------+
| 1 | Services | 0 |
| 2 | Company | 0 |
| 3 | Media Center | 0 |
| 4 | Your Account | 0 |
| 5 | Community | 0 |
| 6 | For Content Publishers | 1 |
| 7 | For Small Businesses | 1 |
| 8 | Background | 2 |
| 9 | Clients | 2 |
| 10 | Addresses | 2 |
| 11 | Jobs | 2 |
| 12 | News | 2 |
| 13 | Press Releases | 3 |
| 14 | Media Kit | 3 |
| 15 | Log In | 4 |
| 16 | Columns | 5 |
| 17 | Colophon | 16 |
| 18 | Cut | 16 |
| 19 | Boombox | 16 |
+----+------------------------+--------+
This is a simple menu structure, with each record identifying a unique node in the menu tree. Each record has a unique record ID, and also contains a parent ID - these two IDs are used to define the parent-child relationships between the branches of the menu tree. So, if I were to represent the data above hierarchically, it would look like this:
<root>
|-Services
|-For Content Publishers
|-For Small Businesses
|-Company
|-Background
|-Clients
|-Addresses
|-Jobs
|-News
|-Media Center
|-Press Releases
|-Media Kit
|-Your Account
|-Log In
|-Community
|-Columns
|-Colophon
|-Cut
|-Boombox
Now, let's suppose I need to display a list of all the nodes in the tree, together with the names of their parents. What I'm looking for is a resultset resembling this:
+--------------+------------------------+
| parent_label | child_label |
+--------------+------------------------+
| Services | For Content Publishers |
| Company | Background |
| Your Account | Log In |
+--------------+------------------------+
If you think about it, you'll see that there's no easy way to obtain this resultset - since all the data is in a single table, a simple SELECT won't work, and neither will one of those complicated outer joins. What I really need here is something called a self join, which allows me to create a second, virtual copy of the first table, and then use a regular inner join to map the two together and get the output I need.
Here's the query I would use:
SELECT a.label AS parent_label, b.label AS child_label FROM menu AS a,
menu AS b WHERE a.id = b.parent;
Here's the output:
+--------------+------------------------+
| parent_label | child_label |
+--------------+------------------------+
| Services | For Content Publishers |
| Services | For Small Businesses |
| Company | Background |
| Company | Clients |
| Company | Addresses |
| Company | Jobs |
| Company | News |
| Media Center | Press Releases |
| Media Center | Media Kit |
| Your Account | Log In |
| Community | Columns |
| Columns | Colophon |
| Columns | Cut |
| Columns | Boombox |
+--------------+------------------------+
Exactly what I need!
Most of the magic here lies in the table aliasing - I've created two copies of the "menu" table, and aliased them as "a" and "b" respectively. This will result in the following two "virtual" tables.
+----+------------------------+--------+
| TABLE a |
+----+------------------------+--------+
| id | label | parent |
+----+------------------------+--------+
| 1 | Services | 0 |
| 2 | Company | 0 |
| 3 | Media Center | 0 |
| 4 | Your Account | 0 |
| 5 | Community | 0 |
| 6 | For Content Publishers | 1 |
| 7 | For Small Businesses | 1 |
| 8 | Background | 2 |
| 9 | Clients | 2 |
| 10 | Addresses | 2 |
| 11 | Jobs | 2 |
| 12 | News | 2 |
| 13 | Press Releases | 3 |
| 14 | Media Kit | 3 |
| 15 | Log In | 4 |
| 16 | Columns | 5 |
| 17 | Colophon | 16 |
| 18 | Cut | 16 |
| 19 | Boombox | 16 |
+----+------------------------+--------+
+----+------------------------+--------+
| TABLE b |
+----+------------------------+--------+
| id | label | parent |
+----+------------------------+--------+
| 1 | Services | 0 |
| 2 | Company | 0 |
| 3 | Media Center | 0 |
| 4 | Your Account | 0 |
| 5 | Community | 0 |
| 6 | For Content Publishers | 1 |
| 7 | For Small Businesses | 1 |
| 8 | Background | 2 |
| 9 | Clients | 2 |
| 10 | Addresses | 2 |
| 11 | Jobs | 2 |
| 12 | News | 2 |
| 13 | Press Releases | 3 |
| 14 | Media Kit | 3 |
| 15 | Log In | 4 |
| 16 | Columns | 5 |
| 17 | Colophon | 16 |
| 18 | Cut | 16 |
| 19 | Boombox | 16 |
+----+------------------------+--------+
Once these two tables have been created, it's a simple matter to join them together, using the node IDs as the common column, and to obtain a list of child and parent labels in the desired format.
/ 229 
