CODEWITHSUNDEEP
mysqlsqlmany-to-manyleft-join
kchason
kchason

Reputation: 2885

Multiple Many to Many Relationships JOINed in MySQL

For a project I'm working on, I am trying to query a time clock but when I LEFT JOIN multiple many to many (or in a single users' record sense, 1 to many) it creates duplicate entries, so when it's grouped by, the aggregate totals are incorrect.

Given the below mock schema: enter image description here

And a query:

SELECT
UserTbl.UserID,
CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
SUM(TIMESTAMPDIFF(MINUTE,  TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
FROM
Users AS UserTbl
LEFT JOIN
TimeClock AS TimeClockTbl
ON UserTbl.UserID = TimeClockTbl.UserID
LEFT JOIN
UserRoles AS UserRoleTbl
ON UserTbl.UserID = UserRoleTbl.UserID
WHERE
UserRoleTbl.RoleID IN (1,2,3)
GROUP BY
UserTbl.UserID
ORDER BY
UserTbl.LastName ASC,
UserTbl.FirstName ASC;

If the user only has 1 role, assigned, it works fine, but if there is a second or third role assigned, it seems to multiply the final result. I considered using a GROUP_CONCAT for the roles and filtering after, but that doesn't seem to be efficient. I also considered subqueries to calculate the clocked in hours for a given user, but I felt that would have the same result. It's also important to note that this is scaled to have a TimeClock table with multiple entries, and a Scheduled table with multiple entries as well.

How can I do this with a decent amount of efficiency?

Upvotes: 0

Views: 252

Answers (1)

Mike
Mike

Reputation: 2005

Simple decision:

SELECT UserTbl.UserID,
       CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
       SUM(TIMESTAMPDIFF(MINUTE, TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
  FROM Users AS UserTbl
  LEFT JOIN TimeClock AS TimeClockTbl ON UserTbl.UserID = TimeClockTbl.UserID
 WHERE UserTbl.UserID IN( SELECT UserID FROM UserRoles WHERE RoleID IN (1,2,3) )
 GROUP BY UserTbl.UserID
 ORDER BY UserTbl.LastName ASC, UserTbl.FirstName ASC;

Concept for similar situations - consistent join:

SELECT A.*,
       SUM(TIMESTAMPDIFF(MINUTE, TimeClockTbl.StartDateTime, TimeClockTbl.EndDateTime)) AS ClockedInMinutes,
       MAX(A.RolesTitle) AS RolesTitle
  FROM (
        SELECT UserTbl.UserID,
               CONCAT_WS(", ", UserTbl.LastName, UserTbl.FirstName) AS UserName,
               FirstName, LastName,
               GROUP_CONCAT(Roles.Title) as RolesTitle
          FROM Users AS UserTbl
          JOIN UserRoles AS UserRoleTbl  ON UserTbl.UserID = UserRoleTbl.UserID
          JOIN Roles ON Roles.RoleID=UserRoleTbl.RoleID
         WHERE UserRoleTbl.RoleID IN (1,2,3)
         GROUP BY UserTbl.UserID
       ) A
  LEFT JOIN TimeClock AS TimeClockTbl ON A.UserID = TimeClockTbl.UserID
 GROUP BY A.UserID
 ORDER BY A.LastName ASC, A.FirstName ASC;

Upvotes: 1

Related Questions