CODEWITHSUNDEEP
mysqlsql
JHamill
JHamill

Reputation: 145

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

Upvotes: 0

Views: 249

Answers (3)

dnagirl
dnagirl

Reputation: 20456

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

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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

Upvotes: 1

Zimbabao
Zimbabao

Reputation: 8240

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

Upvotes: 1

Related Questions