SQL Get first not null value from Parent Rows

Question

Hey everyone,

I've come across this problem a few times now, and I would like to find a better way to solve the problem.

Basically I have a Category which has Sub Categories. If the Sub Category does not have a Description I would like to retrieve the Parent Category Description.

It starts to get difficult when I have Sub Sub Categories.

The Sql Table looks like this:

CategoryID int, ParentCategoryID int Allow Null, Name varchar(255), Description varchar(MAX) Allow Null

I would like to create a function that would look up the Description, but I have no idea if that's the best solution or how to create the function.

I'm mainly looking for the best / right way to solve this issue.

Answer 1

You need two table references and a join, obviously. This would give you access to both descriptions. Then, you would need an inline if statement to choose which value to use, based on the first being null.

Something like this should work (table is named Table1)

SELECT Table1.CategoryID, IIf(Parent.Description Is Null, Table1.description, parent.description) AS [Desc]
FROM Table1 INNER JOIN Table1 AS Parent ON Table1.CategoryID = Parent.ParentCategoryID;

Answer 2

Given your structure, Alex Martelli's solution is probably the best you'll find. Another option, if you can change the model, would be to change from a linked-list tree structure to the nested set model.

Joe Celko has a book on trees and hierarchies which goes into the various ways that you can model them and the advantages/disadvantages of each method. You can also probably find a lot of information on the subject through Google or Google Groups.

One of the disadvantages of the nested set model is that changes to the tree structure are a bit more expensive, but for products you're typically retrieving much more than you're updating. Especially moving things around between categories which is usually rare in most business cases.

Using the nested set model, the following would give you what you want:

SELECT
     P1.Name,
     COALESCE(P1.Description, P2.Description) AS Description
FROM
     Products P1
LEFT OUTER JOIN Products P2 ON
     P2.lft < P1.lft AND
     P2.rgt > P1.rgt AND
     P2.Description IS NOT NULL
LEFT OUTER JOIN Products P3 ON
     P3.lft < P1.lft AND P3.lft > P2.lft AND
     P3.rgt > P1.rgt AND P3.rgt < P2.rgt AND
     P3.Description IS NOT NULL
WHERE
     P3.ID IS NULL

Answer 3

Assumptions:

1. CategoryId is a primary key.

2. If the Description at the row level is null look at parent row. If Parent row has null description, look at parent's parent. Etc. In other words use the first non-null description of ancestors.

3. If row level Description is null, and no ancestor exists with non null Description, overall description is null

Set up example table and test data.

create table #SO (CategoryID int primary key
    , ParentCategoryID int Null
    , Name varchar(255) not null
    , Description varchar(MAX) Null
    )

insert into #SO (CategoryID, ParentCategoryID, Name, Description)
values (1, null, 'Top 1', 'Top 1 Description')
    , (2, null, 'Top 2', 'Top 2 Description')
    , (3, null, 'Top 3', null)
    , (11, 1, 'Child 11', 'Child 11 Description')
    , (12, 1, 'Child 12', null)
    , (21, 2, 'Child 21', null)
    , (211, 21, 'Child 211', null)
    , (2111, 211, 'Child 2111', null)
    , (2112, 211, 'Child 2112', 'Child 2112 Description')
    , (31, 3, 'Child 31', 'Child 31 Description')
    , (32, 3, 'Child 32', null)

Using a recursive CTE. Note that the tree is walked upwards. We start with all rows, and then look at parents as needed instead of doing the normal tree manipulation of starting at the top of the tree and working down.

  ; with Description (BaseCategoryId
     , CurrentParentCategoryId
 , CurrentDescription
 , CurrentLevel)
   as
(-- Anchor -- Start with all rows in the table.
 select CategoryId as BaseCategoryId
 , ParentCategoryId as CurrentParentCategoryId
 , Description as CurrentDescription
 , 0 as CurrentLevel
from #SO  -- Recursive -- We are walking up the tree from all nodes, 
    -- We only continue up the tree when we do not have a description yet.
union all
select D.BaseCategoryId
 , so.ParentCategoryId
 , so.Description
 , D.CurrentLevel + 1
from #SO so
inner join Description D
 on D.CurrentParentCategoryId = so.CategoryId
 and D.CurrentDescription is null)
 select DL.BaseCategoryId as CategoryId
   , DL.CurrentDescription as UltimateDescription
 -- Now self outer join with the CTE every step of the walk
 -- for each BaseCategoryId, and then filter all but the top 
 -- level. (Level is measured as distance from base.)
 from Description as DL
 left outer join Description as DR
on DL.BaseCategoryId = DR.BaseCategoryId
and DL.CurrentLevel < DR.CurrentLevel
 where DR.BaseCategoryId is null 
 order by DL.BaseCategoryId

The output is a mapping from CategoryId to the ultimate description.

In terms of re-use I would make the above a view.

Answer 4

If you need to walk the parent tree recursively, see answer from Alex. If you need one level only, then simple LEFT JOIN should work:

SELECT      c.CategoryID,
            c.ParentCategoryID,
            c.Name,
            COALESCE(c.Description, p.Description) AS Description
FROM        dbo.Category c
LEFT JOIN   dbo.Category p
        ON  c.ParentCategoryID = p.CategoryID