How to Manage Hierarchical Data in MySQL

When you create a MySQL database, each product, customer and orders table contains a list of records that you can traverse in your code. The data is set up in a hierarchical organization, so you query the database starting with the highest ordered table and traverse down to the specified record. You use the MySQL "Join" statement to link each table in your queries.

Instructions

1. Open the MySQL Query Browser program from the Windows program menu. Log in to your database server.

2. Click the "Query" button to open the editor. You create your MySQL queries in the editor to view and test your SQL code and data.

3. Create a query that starts with the first, main table and traverses to a lower table to retrieve the data. For instance, a customer is the main table in an ecommerce business. You use the "join" statement to join to the "orders" table from the "customers" table. The following code is an example of a hierarchical query:

   select * from customers c
   join orders o on c.customerid=o.customerid
   where c.customerid=111

   In this example, the customer number 111 is returned and all associated orders are returned along with the customer data.

4. Click the "Execute" button to run the query. If two orders were made, two records display. In front of each order is the customer's information, because the "join" statement links the hierarchical data.

References

• Pure Performance: Managing Hierarchical Data in SQL