Self Join query question

[Convoy]

I read about self join queries and the example given was
that of an employee table that has a superviser field.
This field is populated from the same table using a self
join. Is it possible using this arrangement to have more
than one supervisor, that is, create a one to many self
join?

 

[Brian Camire]

It sounds like what you want is a many-to-many relationship between
employees and supervisors. In other words, an employee can report to many
supervisors, and a supervisor can have many employees report to them.

You can represent this kind of relationship using a second table, sometimes
called a "junction table". For example, with an Employees table something
like this:

Employee ID, Employee Name
1, Jane Doe
2, John Smith
3, Frank Brown
4, Mabel Schwartz

the junction table (maybe called Employee Supervisors) might look something
like this:

Employee ID, Supervisor Employee ID
1, 2
1, 4
2, 3
3, 4

with a primary key (or at least a unique constraint/index) on the
combination of Employee ID and Supervisor Employee ID. So Jane reports to
both John and Mabel, John reports to Frank, and Frank reports to Mabel.