Self recursive relationship

  • Thread starter Thread starter placek
  • Start date Start date
P

placek

Hi

I have two tables. The first has fields: Employee_ID,
Employee_name, Employee_Job_Title, Manager_ID.

The second has fields: Manager_ID, Manager_name.

(Please note The Manager_ID is just the Employee_ID, and
the Manager_ID fields are related)

I want to create a query with format

Employee_ID, Employee_Name, Manager_ID, Manager_name

Is this the right way to resolve this self-recursive
relationship in order to create this query?

Thanks, Martin
 
To tie your two tables together, you could use a non-
recursive basic SELECT query like so: (I named your
tables Employees and Managers, by the way)
SELECT Employees.Employee_ID, Employees.Employee_name,
Employees.Manager_ID, Managers.Manager_name
FROM Managers INNER JOIN Employees ON Managers.Manager_ID
= Employees.Manager_ID;

But you probably don't even need the Managers table at
all IF all the Managers are in the Employees table. In
that case you could tie the ManagerID to the Employee ID
in the same table like this:
SELECT Employees.Employee_ID, Employees.Employee_name,
Employees_1.Employee_ID AS ManagerID,
Employees_1.Employee_name AS ManagerName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.Manager_ID = Employees_1.Employee_ID;

(Note: For the UberBoss you could add an employee
called "No Manager" [Employee_ID = 0?] and put that in
his Manager_ID field. Otherwise he just won't show up in
queries.)
 
Is this the right way to resolve this self-recursive
relationship in order to create this query?
Click to expand...

I don't think it is recursive. A self-join would be where the
Employee.ManagerID was a fk referencing Employees(EmployeeID). As it is, it
seems that it references a different table. It probably is possible to set
up situations where something in the Managers table references the
Employees table, but the mathematics is quite different there and I am not
sure you would get what you want.

Does that help?


Tim F
 
-----Original Message-----
To tie your two tables together, you could use a non-
recursive basic SELECT query like so: (I named your
tables Employees and Managers, by the way)
SELECT Employees.Employee_ID, Employees.Employee_name,
Employees.Manager_ID, Managers.Manager_name
FROM Managers INNER JOIN Employees ON Managers.Manager_ID
= Employees.Manager_ID;

But you probably don't even need the Managers table at
all IF all the Managers are in the Employees table. In
that case you could tie the ManagerID to the Employee ID
in the same table like this:
SELECT Employees.Employee_ID, Employees.Employee_name,
Employees_1.Employee_ID AS ManagerID,
Employees_1.Employee_name AS ManagerName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.Manager_ID = Employees_1.Employee_ID;

(Note: For the UberBoss you could add an employee
called "No Manager" [Employee_ID = 0?] and put that in
his Manager_ID field. Otherwise he just won't show up in
queries.)

-----Original Message-----
Hi

I have two tables. The first has fields: Employee_ID,
Employee_name, Employee_Job_Title, Manager_ID.

The second has fields: Manager_ID, Manager_name.

(Please note The Manager_ID is just the Employee_ID, and
the Manager_ID fields are related)

I want to create a query with format

Employee_ID, Employee_Name, Manager_ID, Manager_name

Is this the right way to resolve this self-recursive
relationship in order to create this query?

Thanks, Martin
.
Click to expand...
.
Many Thanks
Click to expand...
 
-----Original Message-----


I don't think it is recursive. A self-join would be where the
Employee.ManagerID was a fk referencing Employees (EmployeeID). As it is, it
seems that it references a different table. It probably is possible to set
up situations where something in the Managers table references the
Employees table, but the mathematics is quite different there and I am not
sure you would get what you want.

Does that help?


Tim F

.
Yes, thanks.
Click to expand...
 
Back
Top