如何从同一层次表中从一个值或孩子中摘取父母和孩子

Question

考虑以下表：

表：类别

CategoryID int identity PK <--.
ParentID int NULL FK --->-----'
Name varchar(50)

它允许同一表中的父/子关系：

CategoryID   ParentID   Name
--------------------------------
1            NULL       Parent A
2            1          Child A

表：project

ProjectID int identity PK
Name varchar(50) NOT NULL

我还有一个用于项目和类别的交界表

表：ProjectCategory

ProjectID int PK FK
CategoryID int PK FK
ProjectID    CategoryID
--------------------------------
1            2 <-- Either a parent or child category

一个项目可以分配给父级类别或子类别，但不能两者兼而有之。从本质上讲，如果将项目分配给子类别，则该项目与父母类别有关系，无论如何，我都不希望这样做：

ProjectID    CategoryID
--------------------------------
1            1 <-- Parent
1            2 <-- Child of Parent 1

这一切都很好，但是对于我的应用程序，我需要弄平事物，并具有看起来像这样的结果/行：

ProjectID    ParentCategoryID   ChildCategoryID
-----------------------------------------------
1            1                  2

因此，问题是，我不确定为应用程序提供同时提供两者的最佳方法，但是它需要在单个SQL查询中完成，从而导致一行数据。

update

我向自己发布了一个答案，但是如果有更好的方法，请将其拆开。

Answer 1

我可能找到了自己的答案，但是如果有更好的方法，我会很快采取更好的答案。

SELECT p.*
      ,ISNULL(cat.ParentID, cat.CategoryID) AS ParentCategoryID
      ,CASE WHEN cat.ParentID IS NOT NULL THEN cat.CategoryID
       END ChildCategoryID
FROM Project p
OUTER APPLY (
    SELECT TOP 1 c.CategoryID, c.ParentID FROM ProjectCategory pc
    INNER JOIN Category c ON c.CategoryID = pc.CategoryID
    WHERE pc.ProjectID = p.ProjectID
) cat

Answer 2

我假设您在parentid到categoryId的类别表上具有自我参考外键。您确实使描述中的事情变得复杂了，因为parentId也将以类别形式存在，您所需要的只是两个表之间的简单连接：

select pc.ProjectID,
       coalesce(c.ParentID, c.CategoryID) as ParentCategoryID --shows category id as parent if it has no parent
       case when c.ParentID is not null then c.CategoryID end as ChildCategoryID
from   ProjectCategory as pc
inner  join Category as c
on     c.CategoryID = pc.CategoryID