Может ли кто-нибудь показать мне, почему мой SQL-запрос не работает (см. подробности)?

Я бы немного переформулировал запрос....

SELECT
    duplicates.NumOccurrences,
    duplicates.userID,
    users.firstname,
    users.lastname
FROM (
    SELECT
        userID,
        COUNT(userID) AS NumOccurrences
    FROM userDepartments
    GROUP BY userID
    HAVING COUNT(userID) > 1
) duplicates
INNER JOIN users ON duplicates.userID = users.userID

Механизм SQL не знает, что у вас есть только одно имя пользователя для каждого идентификатора пользователя, поэтому вам нужно группировать по имени и фамилии, а также по идентификатору пользователя.

SELECT userDepartments.userID, users.firstname, users.lastname,
COUNT(userID) AS NumOccurrences
FROM userDepartments INNER JOIN users ON userDepartments.userID = users.userID
GROUP BY userID, users.firstname, users.lastname
HAVING ( COUNT(userID) > 1 )

Если вы не группируете по имени и фамилии, механизм не знает, что он должен делать, если он получает более одного значения имени для данного идентификатора пользователя. Указав ему сгруппировать по всем трем значениям, он знает, что если для каждого идентификатора пользователя имеется более одной строки, он должен вернуть все эти строки. Хотя этого не должно происходить, в данном случае движок недостаточно умен, чтобы решить это самостоятельно.

Вы также можете сделать это следующим образом:

SELECT users.userId, users.firstname, users.lastname, departments.NumOccurrences
FROM users INNER JOIN (
     SELECT userId, count(userId) as NumOccurrences 
     FROM userDepartments 
     GROUP BY userID 
     HAVING ( COUNT(userID) > 1 )
) departments ON departments.userID = users.userID

Сгруппируйте по всем трем параметрам: userDepartments.userID, users.firstname и users.lastname.

Вам необходимо включить user.firstname и users.lastname в предложение GROUP BY, поскольку они не являются агрегированными значениями (обратите внимание, что MySQL действительно поддерживает синтаксис, который вы использовали в своем запросе, но он не является стандартным).

Если вы выполняете «группировку», то все в части «выбрать» должно быть:

1. Упоминается в предложении «group by» или

2. Результат агрегатной функции (например, count())

Я бы сделал это так (в Oracle, на случай, если это не работает в вашей системе):

SELECT users.userID, users.firstname, users.lastname, NumOccurrences
  FROM users
       INNER JOIN (
         SELECT userID, COUNT(userID) AS NumOccurrences
           FROM userDepartments
           GROUP BY userID
           HAVING ( COUNT(userID) > 1 )
       ) d
       ON d.userID = users.userID

Добавьте свой user.Firstname и User.lastname в свою группу по предложению

Я вижу много хороших заметок о добавлении полей вашего имени в группу. Я думаю, что я бы сделал это так, хотя:

SELECT Users.*, dups.NumOccurances, ud.DepartmentName
FROM Users
INNER JOIN
  (
    SELECT userID, COUNT(userID) AS NumOccurrences
    FROM userDepartments
    GROUP BY userID
    HAVING ( COUNT(userID) > 1 )
  ) dups ON dups.userID = Users.UserID
INNER JOIN userDepartments ud ON ud.UserID=Users.UserID
ORDER BY Users.LastName, Users.FirstName, Users.UserID

Одна из причин такого подхода заключается в том, что с его помощью легче вернуться назад и получить любую другую информацию, которая вам может понадобиться.