Self-joins - MySQL

The self-join provides the administrator with a powerful method of centralizing relational data to a single table. In fact, the self-join is performed by joining a particular table to itself. Let's illustrate this concept with an example:

Suppose we are in control of a large database containing information regarding various pieces of hardware used to build a computer workstation. A workstation may consist of a desk, pc, monitor, keyboard and mouse. Furthermore, the desk can be considered the 'parent' of all other parts of the workstation. We want to keep accurate records of each workstation, so we will correlate all parts of a specific workstation together via a unique id number. Actually, each part will contain two id numbers, one unique to that specific item, and one identifying its' parent (the desk) id number.

Assume that this is our table:

uniq_id	name	parent_id
d001	desktop	null
m4gg	monitor	d001
k245	keyboard	d001
pc345	200mhz pc	d001
d002	desktop	null
m156	monitor	d002
k9334	keyboard	d002
pa556	350 mhz pc	d002

Notice that the desktop does not have a parent_id, since it is in fact the parent for all of its' corresponding parts. With the table filled with data, we can now begin querying it for useful information. Also note that while our table is simple for reason of best illustration of use of the self-join, one could provide significantly more useful information regarding each item.

mysql> select t1.*, t2.* from page5 as t1, page5 as t2;

So what is the outcome? Like previously seen with such as join regarding two tables, each row from the first table will be matched with every row in the second table. Try it and see. Again, however, this is not very useful to us. Let's look at a more interesting example:

We are interested in viewing information regarding a specific workstation in which we several technical support calls had been made. We know what the particular workstation id is (the desk id). Let's query the database to pull up all relevant pieces of this workstation:

mysql> select parent.uniqid, parent.name, child.uniqid, child.name
    -> from page5 as parent, page5 as child
    -> where child.parent_id = parent.uniqid AND parent.uniqid = "d001";

This provides a much more interesting outcome, displayed as follows:

uniqid	name	uniqid	name
d001	desktop	m4gg	monitor
d001	desktop	k245	keyboard
d001	desktop	pc345	200 mhz pc

The self-join is also used as an efficient method of verifying table data. Since the uniqid column within the table is intended to be unique, it would not be good if the data-entry dept. accidentally entered two items with the same uniqid into the database. This could be periodically checked by using a self-join. Assume that we modified the 350 mhz pc uniqid to be 'm156' (which is incidentally the uniqid value of the monitor belonging to workstation 'd002'). Consider the following example:

mysql> select parent.uniqid, parent.name, child.uniqid, child.name
    -> from page5 as parent, page5 as child
    -> where parent.uniqid = child.uniqid AND parent.name <> child.name

This would result in the following:

uniqid	name	uniqid	name
m156	350 mhz pc	m156	monitor
m156	monitor	m156	350 mhz pc

There you have it. Table joins made easy. Try playing around with variations of the commands highlighted within this article to gain a clear understanding of the syntax. Once this is understood, you will find that table joins will play an integral part in your development activities. Be sure to check out MySQL's various discussion groups (http://www.mysql.com), as there is usually quite a bit of information exchanged regarding table joins.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus