SQL Server: Select Parent-Child

Question

I have SQL Server 2008 with a table called ProductCategories designed like this:

Id | Name      | ParentId
71   PCs         NULL
32   MACs        NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I would like to select from this table, and get a result set like this:

Id | Name      | ParentId
71   PCs         NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
32   MACs        NULL
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I tried this, but that obviously gives me the ones with no ParentId first:

WITH Hierarchy
AS
(
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    WHERE 
        T1.parentid IS NULL OR 
        T1.parentid IN (SELECT id from ProductCategories WHERE parentid IS NULL)
    UNION ALL
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    INNER JOIN 
        Hierarchy TH ON TH.Id = T1.ParentId
)
select *
from Hierarchy 
order by parentid

Please help me, if you can :)

-- The guy who doesn't know SQL

What (in english) are you trying to do ? I can't reverse engineer your intent from the output you included. There are an infinite number of ways to generate that output from the data, but probably very few that would generate the "right" output (that does what you want) from a different sdet of data... — Charles Bretana, Sep 09 '09 at 13:37
How does your result set differ from the raw data in the table, other than an undocumented sort order? — D'Arcy Rittich, Sep 09 '09 at 13:39
To clear this up: this table is referring to itself? Yep, apparently it does. — Wim ten Brink, Sep 09 '09 at 13:40
ahh I see it now, the values in the third column should be 71 and 32, not 1 & 2 — Charles Bretana, Sep 09 '09 at 13:43

Charles Bretana · Accepted Answer · 2009-09-09T13:47:01.007

3

try this:

Select Id, Name, ParentId
From ProductCategories
Order By Coalesce(ParentId, Id), 
   Coalesce(ParentId, 0), Name

Three Order By clauses,

Coalesce(ParentId, Id): This one groups the records by the parent, for both the parent itself and all the children of that parent
Coalesce(ParentId, 0) This groups within each set so that the one record with a null parent (the parent) sorts to the top within the group
Name, This sorts the children within the group by name

edited Sep 09 '09 at 13:47

answered Sep 09 '09 at 13:41

Charles Bretana

143,358
22
150
216

Perfect. Just what I was looking for. – MartinHN Sep 09 '09 at 14:09

score 0 · Answer 2 · answered Sep 09 '09 at 13:41

0

Try this

SELECT id, name, parentId 
FROM categories
ORDER BY ISNULL(parentId,id), id

Btw, shouldn't first two indexes in your table be 1 and 2, not 71 and 32 ?

answered Sep 09 '09 at 13:41

Lukasz Lysik

10,462
3
51
72

No. You can add and delete categories, and later on promote a new category to a parent category. So that would be the case with 71 and 32. – MartinHN Sep 09 '09 at 14:08

SQL Server: Select Parent-Child

2 Answers2