The purpose of independent associations
I am learning to program with SQL and have just been submitted for do-it-yourself join. I understand how it works, but I don't understand what is their purpose other than special use, attaching an employee table to itself to neatly display employees and their respective managers.
This use can be demonstrated in the following table:
EmployeeID | Name | ManagerID
------ | ------------- | ------
1 | Sam | 10
2 | Harry | 4
4 | Manager | NULL
10 | AnotherManager| NULL
And the next request:
select worker.employeeID, worker.name, worker.managerID, manager.name
from employee worker join employee manager
on (worker.managerID = manager.employeeID);
What will be returned:
Sam AnotherManager
Harry Manager
Also, are there any other circumstances where self-joining would be beneficial? I cannot figure out a scenario in which I need to do my own merge.
source to share
Your example is good. Self-joins are useful when the table contains a foreign key in itself. The employee has a manager and the manager has another employee. So self-joining makes sense.
Many hierarchies and relationship trees are well suited for this. For example, you might have a parent organization divided into regions, groups, teams, and offices. Each of these can be saved as an "organization" with the parent ID as a column.
Or maybe your business has a referral program and you want to record which customer reached out to someone. They are both "clients" in one table, but they have an FK reference to another.
Hierarchies that are not suitable for this are those where an object can have more than one "parent" reference. For example, suppose you had facebook style data that recorded all users and friendship links with other users. This could fit into this model, but then you need a new "user" row for every friend the user had, and every row will be duplicate except for the "connectionUserID" column or whatever you name it.
In a many-to-many relationship, you will likely have a separate “relationship” table with a from and to column and possibly a column indicating the type of relationship.
source to share
I found myself most helpful in situations like this:
Get all the employees who work for the same manager as Sam. (It doesn't have to be hierarchical, it can also be: Get all employees working in the same location as Sam)
select e2.employeeID, e2.name
from employee e1 join employee e2
on (e1.managerID = e2.managerID)
where e1.name = 'Sam'
It is also helpful to find duplicates in a table, but this can be very inefficient.
source to share
There are some great examples of using self-launch here. The one I use often refers to "scheduling". I work with learning times, but they are relevant in other cases as well.
I am using self-joins to find out if two elements collided with each other, for example. the student is scheduled for two lessons at the same time, or the room is booked for two. For example:
CREATE TABLE StudentEvents(
StudentId int,
EventId int,
EventDate date,
StartTime time,
EndTime time
)
SELECT
se1.StudentId,
se1.EventDate,
se1.EventId Event1Id,
se1.StartTime as Event1Start,
se1.EndTime as Event1End,
se2.StartTime as Event2Start,
se2.EndTime as Event2End,
FROM
StudentEvents se1
JOIN StudentEvents se2 ON
se1.StudentId = se2.StudentId
AND se1.EventDate = se2.EventDate
AND se1.EventId > se2.EventId
--The above line prevents (a) an event being seen as clashing with itself
--and (b) the same pair of events being returned twice, once as (A,B) and once as (B,A)
WHERE
se1.StartTime < se2.EndTime AND
se1.EndTime > se2.StartTime
Similar logic can be used to find other things in the "timetable data" such as a pair of trains that can be taken from A to B to C.
source to share
Self-joins are useful when you want to compare records in the same table with each other. Examples are: finding duplicate addresses, finding customers where the shipping address does not match the invoice address, comparing the total in the daily report (saved as a record) with the total of the previous day, etc.
source to share