Adjacency Model MySQL syntax

I have made use of the adjacency model to represent a child/parent relationship.

It is being used to mirror a companies hierarchy, where each employee has a manager. Both employees and managers are stored in the same table - so the managerID field refers to the userID of a particular manager.

Below is my table structure: userID - email - password - firstName - lastName - officeID - departmentID - managerID - roleID - username

So managerID is a foreign key but originates from the same schema.

What I am attempting to do is display a table containing information regarding users of the system - including who their manager is.

So far I hav开发者_运维知识库e achieved this using multiple joins with the following SQL statement:

SELECT user.firstName, user.lastName, office.officeName, department.departmentTitle, role.roleName
FROM user, office, department, role
WHERE user.officeID = office.officeID
AND user.departmentID = department.departmentID
AND user.roleID = role.roleID

However, I am unsure on how to display the names of the managers.

Any help would be appreciated.

Your query only pulls users. To get the manager name you need to JOIN the user table again, like so:

SELECT u.firstName, u.lastName, 
       o.officeName, d.departmentTitle, 
       r.roleName, 
       m.firstName AS mFN, m.lastName AS mLN
FROM user u JOIN office o ON (u.officeID = o.officeID)
            JOIN department d ON (u.departmentID = d.departmentID)
            JOIN role r ON (u.roleID = r.roleID)
            JOIN user m ON (u.manager_id=m.user_id);

Just join user table once again with managerID=m.userID condition

m.firstName and m.lastName are manager's name

SELECT user.firstName, user.lastName, m.firstName,m.lastName, office.officeName, department.departmentTitle, role.roleName
FROM user as u, user as m, office, department, role
WHERE u.officeID = office.officeID
AND u.departmentID = department.departmentID
AND u.roleID = role.roleID
AND u.managerID=m.userID

If every user has a manager (and managerID is not null), you can safely change the LEFT JOIN into inner JOIN.

SELECT u.firstName
     , u.lastName
     , office.officeName
     , department.departmentTitle
     , role.roleName
     , manager.firstName AS managerFirstName
     , manager.lastName AS managerLastName
  FROM user AS u
    JOIN office
      ON u.officeID = office.officeID
    JOIN department
      ON u.departmentID = department.departmentID
    JOIN role
      ON u.roleID = role.roleID
    LEFT JOIN user AS manager
      ON u.managerID = manager.userID