Home arrow MySQL arrow Page 9 - Understanding SQL Joins

Up A Tree - MySQL

Left join. Right join. Inner join. If you've ever wondered what all this jargon means, it's time to find out. Welcome to the wild, the wacky, the insanely cool world of SQL joins.

TABLE OF CONTENTS:
  1. Understanding SQL Joins
  2. Meeting The Family
  3. Keeping It Simple
  4. Crossed Wires
  5. Finding Common Ground
  6. One Step Left...
  7. ...Two Steps Right
  8. The Bookworm Turns
  9. Up A Tree
  10. A Long Goodbye
By: The Disenchanted Developer, (c) Melonfire
Rating: starstarstarstarstar / 247
August 20, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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.

 
 
>>> More MySQL Articles          >>> More By The Disenchanted Developer, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: