Запрос на присоединение, который получает только 1 подходящих строк

У меня есть следующие две таблицы:

пользователь

  • Идентификатор пользователя
  • название
  • Тип

UserCompany

  • Идентификатор компании
  • Идентификатор пользователя

Компания

  • Идентификатор компании
  • название

Я хочу, чтобы все компании имели только 1 пользователя с типом 5.

Почему этот запрос не работает?

SELECT C.Name FROM USER u 
JOIN UserCompany UC on u.UserID = UC.UserID 
JOIN Company C on uc.companyID = c.companyID
WHERE u.Type = 5
GROUP BY u.name
HAVING COUNT(u.name) = 1

ПРИМЕР ДАННЫХ

Таблица пользователей:

1 John 1
2 Beth 2 
3 Mike 5
4 John 5

Таблица компании:

1 XYZ 
2 KBC
3 MMM

UserCompany

1 1 
1 3
1 4
2 3
2 1

=> Компания 2 должна быть возвращена, потому что у нее есть только 1 пользователь с типом 5

Источник

2 ответа

Решение

Итак, я пересоздал ваши примеры таблиц, чтобы мы могли использовать их в SSMS;

IF OBJECT_ID('tempdb..#User') IS NOT NULL DROP TABLE #User
CREATE TABLE #User (UserID int, Name nvarchar(4), Type int)
INSERT INTO #User (UserID, Name, Type)
VALUES
 (1,'John',1)
,(2,'Beth',2)
,(3,'Mike',5)
,(4,'John',5)

IF OBJECT_ID('tempdb..#Company') IS NOT NULL DROP TABLE #Company
CREATE TABLE #Company (CompanyID int, Name nvarchar(3))
INSERT INTO #Company (CompanyID, Name)
VALUES
 (1,'XYZ')
,(2,'KBC')
,(3,'MMM')

Я должен был угадать заголовки ваших столбцов, поскольку они не включены в пример данных. Это было интересно, так как я догадался, что стол UserCompany имел UserID и CompanyID в том же порядке, что и заголовок. Когда я посмотрел на это, это показывает, что у вас вообще нет пользователя 3 в данных. В целях этого я собираюсь предположить, что это на самом деле CompanyID и UserID в этом порядке.

IF OBJECT_ID('tempdb..#UserCompany') IS NOT NULL DROP TABLE #UserCompany
CREATE TABLE #UserCompany (CompanyID int, UserID int)
INSERT INTO #UserCompany (CompanyID, UserID)
VALUES
 (1,1)
,(1,3)
,(1,4)
,(2,3)
,(2,1)

Предполагая, что это правильно, вы захотите сделать что-то вроде этого;

SELECT
    c.Name AS Company_Name
    ,COUNT(u.UserID) Employees
FROM #Company c
INNER JOIN #UserCompany uc 
    ON c.CompanyID = uc.CompanyID
INNER JOIN #User u 
    ON uc.UserID = u.UserID
    AND u.Type = 5
GROUP BY c.Name
HAVING Count(u.UserID) > 1

Который даст вам выходной;

Company_Name    Employees
XYZ             2
Источник

Я бы просто обновил HAVING пункт:

SELECT UC.companyID 
FROM USER u JOIN 
     UserCompany UC 
     ON u.UserID = UC.UserID JOIN 
     Company C 
     ON uc.companyID = c.companyID
GROUP BY UC.companyID
HAVING SUM(CASE WHEN U.TYPE = 5 THEN 1 ELSE 0 END) = 1;
Источник
Другие вопросы по тегам