Self recursive relationship

[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

 

[Guest]

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.)

 

[Tim Ferguson]

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

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

 

[placek]

-----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
.

.
Many Thanks

 

[Placek]

-----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.