Chapter 2 : Recursive association

In this chapter we are going to discuss on how to do join between a table and that table itself, which is known under self-join. We are going to discuss about it specifically in the context of recursive association. As we are going to see, this kind of association describes a data dependency.

Figure 2.1 : Database schema

1. Top level

Let’s assume we’d like to get data about the big boss of the company, namely the employee having no boss !

SELECT First_name, Last_name
FROM Worker
WHERE Boss_id IS NULL

We get the following table.

2. First level

Let’s assume we’d like to get data about employees whose direct boss is Eddy Fisher (this could also be done by using a subquery).

SELECT W2.First_name, W2.Last_name, W1.Last_name AS DirectBoss
FROM Worker AS W1, Worker AS W2
WHERE W2.Boss_id = W1.ID
      AND W1.First_name = 'Eddy' AND W1.Last_name ='Fisher'

We get the following table.

3. Second level

Let’s assume we’d like to get data about employees whose boss is directly subordinated to Eddy Fisher. In other words, data about the second level employees in the hierarchy.

SELECT W3.First_name, W3.Last_name, W2.Last_name AS DirectBoss
FROM Worker AS W1, Worker AS W2, Worker AS W3
WHERE W3.Boss_id = W2.ID
      AND W2.Boss_id = W1.ID
      AND W1.First_name = 'Eddy' AND W1.Last_name ='Fisher' 

We get the following table.