CODEWITHSUNDEEP
sqlsql-servert-sql
user1447679
user1447679

Reputation: 3260

How to fetch both parent and child from same hierarchical table, from one value, which may be parent or child?

Consider the following tables:

Table: Category

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

It allows a parent/child relationship in the same table:

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

Table: Project

ProjectID int identity PK
Name varchar(50) NOT NULL

I also have a junction table for projects and categories

Table: ProjectCategory

ProjectID int PK FK
CategoryID int PK FK

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

A project can be assigned to either a parent category, or a child category, but not both. In essence, if a project is assigned to a child category, it's relationally tied to the parent category regardless, and I don't want this:

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

That's all fine, but for my application I need to flatten things out and have a result set/row that looks like this:

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

So the issue is, I'm not sure of the best way to provide both for the application, but it needs to be done in a single SQL query that results in a single row of data.

Update

I posted an answer to myself, but please tear it apart if there's a better way.

Upvotes: 0

Views: 80

Answers (2)

Anand
Anand

Reputation: 1123

I'm assuming you have self referential foreign key on Category table from ParentID to CategoryID. You are really complicating things in your description, since the ParentID will also exist as a CategoryID, all you need is a simple join between the two tables:

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

Upvotes: 0

user1447679
user1447679

Reputation: 3260

I may have found my own answer, but I'll quickly take a better one if there's a better way.

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

Upvotes: 1

Related Questions