SQL Self-Join Результаты внутри основного результата (не отдельный столбец)

Использование: SQL Server 2012

У меня есть таблица саморегуляции под названием « Department :

 DepartmentID | Name | DepartmentParentID 1 Sales NULL 2 Outbound 1 3 Inbound 1 4 Showroom 1 

В отделах, таких как Inbound and Showroom есть родительский отдел Sales .

Мне нужно написать SQL-запрос, который покажет, какие пользователи находятся в каких отделах. Что-то вроде этого:

 SELECT d.DepartmentID, d.DepartmentName, d2.DepartmentParentID, d2.DepartmentName FROM [User_Department] ud ---table that maps users to departments INNER JOIN [Department] d1 ON d1.DepartmentID = ud.DepartmentID LEFT JOIN [Department] d2 ON d2.DepartmentID = d1.DepartmentParentID ---this has gone horribly wrong WHERE ud.Username = @Username 

Забыв часть LEFT JOIN в приведенном выше коде, я бы получил такой результат:

 Username | DepartmentName DannySmith Inbound DannySmith Outbound 

Но мне нужен родительский отдел, который также отображается в списке DepartmentName (это то, что я хочу) :

 Username | DepartmentName DannySmith Sales DannySmith Inbound DannySmith Outbound 

Как это можно достичь. Я не хочу, чтобы родительский отдел находился в отдельном столбце ( это то, чего я НЕ хочу ):

 Username | DepartmentName | DepartmentName DannySmith Inbound Sales DannySmith Outbound Sales 

Пробовал искать некоторые книги SQL, но не могу найти ничего, что могло бы мне помочь. Кто-нибудь, пожалуйста?

Если у вас есть возможность использовать несколько уровней иерархии, вы должны использовать рекурсивное общее табличное выражение. Например, если в Showroom, Inbound или Outbound могут быть дочерние подразделения.

 ;WITH CTE AS ( SELECT ud.UserName, d.DepartmentID, d.Name, d.DepartmentParentID FROM [User_Department] ud INNER JOIN [Department] d ON d.DepartmentID = ud.DepartmentID UNION ALL SELECT CTE.UserName, d.DepartmentID, d.Name, d.DepartmentParentID FROM [Department] d INNER JOIN [CTE] on CTE.DepartmentParentId = d.DepartmentID ) SELECT distinct UserName, DepartmentId, Name FROM CTE WHERE Username = 'DannySmith' 

Если вы точно знаете, что существует только один уровень иерархии, ответ, который дал @sgeddes, будет работать.

Один из вариантов – использовать UNION (это также использует общее табличное выражение, которое облегчает его чтение):

 WITH CTE AS ( SELECT ud.UserName, d.DepartmentID, d.DepartmentName, d.DepartmentParentID FROM [User_Department] ud INNER JOIN [Department] d ON d.DepartmentID = ud.DepartmentID WHERE ud.Username = @Username ) SELECT UserName, DepartmentId, DepartmentName FROM CTE UNION SELECT C.UserName, D.DepartmentId, D.DepartmentName FROM Department D JOIN CTE C ON D.DepartmentId = C.DepartmentParentID 
  • Демо-версия SQL Fiddle

BTW. Если я правильно истолковал ваши определения таблиц, ваше поле UserName должно, вероятно, находиться в собственной таблице User .

  • Проблемы с созданием полного текстового индекса в представлении
  • Выберите 1 запись из многих за период времени
  • Различия между знаками равенства (=) и IN с подзапросом
  • Преобразование XML в переменную таблицы
  • Используйте EXISTS с производной таблицей
  • Автоматическая процедура для SQL-запроса
  • Как вычислить префикс маршрута IPv6 CIDR в SQL?
  • Использование с CUBE в одном столбце дважды для вывода результатов с перкутантом
  • Мне нужно создать представление, которое объединяет все таблицы на основе их префикса (новые таблицы добавляются ежемесячно)
  • Рекомендации по индексам и отсутствующим указателям в SSMS
  • Отправка писем из хранимых процедур SQL Server 2012
  • Давайте будем гением компьютера.