Conditional SQL Select Statement

Question

I have a 'Categories' table. Each row has a 'CategoryId' (required), and a 'ParentCategoryId' (not required). This allows for a Parent-->Child relationships between categories.

What I'm trying to do is select all categories, but if a parent-->child relationship exists, select only the parent.

Here's what I'm currently trying, but it's taking forever, and is just flat wrong. Disclaimer, SQL is NOT my strong suit!

declare @ProjectId int
set @ProjectId = 1

declare @catid int
declare @catname nvarchar(100)
declare @catprojid int
declare @catparentid int
declare @sortorder int

declare db_cursor cursor for
select categoryid,categoryname,projectid,parentcategoryid,sortorder from dbo.ProjectCategories
where ProjectId = @ProjectId

open db_cursor
fetch NEXT from db_cursor into @catid,@catname,@catprojid,@catparentid,@sortorder

while @@FETCH_STATUS = 0
begin
    if @catparentid != null select * from dbo.ProjectCategories where CategoryId = @catparentid
    else select @catid,@catname,@catprojid,@catparentid,@sortorder
end

close db_cursor
deallocate db_cursor
go

Answer 1

The following select retrieves all the Categories that are parents to at least one other Category:

declare @ProjectId int
set @ProjectId = 1

select distinct
    parent.categoryid, 
    parent.categoryname, 
    parent.projectid, 
    parent.parentcategoryid, 
    parent.sortorder 
from dbo.ProjectCategories parent
join dbo.ProjectCategories child on child.ParentCategoryId = parent.CategoryId
where parent.ProjectId = @ProjectId

Answer 2

This will list all of the parents who have children. Parents w/o children will not be displayed.

DECLARE @ProjectCategories TABLE
(
    categoryid          INT,
    categoryname        VARCHAR(10),
    projectid           INT,
    parentcategoryid    INT,
    sortorder           INT
)

INSERT INTO @ProjectCategories
SELECT 1, 'Main1',  1, null,    1 UNION ALL
SELECT 2, 'Child1', 1, 1,       1 UNION ALL
SELECT 3, 'Child2', 1, 2,       1 UNION ALL
SELECT 4, 'Child3', 1, 3,       1 UNION ALL
SELECT 5, 'Child4', 1, 4,       1 UNION ALL
SELECT 6, 'MainA',  2, null,    1 UNION ALL
SELECT 7, 'ChildA', 2, 6,       1 UNION ALL
SELECT 8, 'ChildB', 2, 7,       1 UNION ALL
SELECT 9, 'Main!',  3, null,    1 

SELECT * 
FROM @ProjectCategories Child
    INNER JOIN @ProjectCategories Parent
        ON Child.parentcategoryid = Parent.categoryid
            AND Parent.parentcategoryid IS NULL

Here are all parents regardless if there are children:

DECLARE @ProjectCategories TABLE
(
    categoryid          INT,
    categoryname        VARCHAR(10),
    projectid           INT,
    parentcategoryid    INT,
    sortorder           INT
)

INSERT INTO @ProjectCategories
SELECT 1, 'Main1',  1, null,    1 UNION ALL
SELECT 2, 'Child1', 1, 1,       1 UNION ALL
SELECT 3, 'Child2', 1, 2,       1 UNION ALL
SELECT 4, 'Child3', 1, 3,       1 UNION ALL
SELECT 5, 'Child4', 1, 4,       1 UNION ALL
SELECT 6, 'MainA',  2, null,    1 UNION ALL
SELECT 7, 'ChildA', 2, 6,       1 UNION ALL
SELECT 8, 'ChildB', 2, 7,       1 UNION ALL
SELECT 9, 'Main!',  3, null,    1 

SELECT * 
FROM @ProjectCategories Parent
WHERE Parent.parentcategoryid IS NULL

Here's a recursive CTE which will get all of the children for the parent no matter how many children there are.

DECLARE @ProjectCategories TABLE
(
    categoryid          INT,
    categoryname        VARCHAR(10),
    projectid           INT,
    parentcategoryid    INT,
    sortorder           INT
)

INSERT INTO @ProjectCategories
SELECT 1, 'Main1',  1, null,    1 UNION ALL
SELECT 2, 'Child1', 1, 1,       1 UNION ALL
SELECT 3, 'Child2', 1, 2,       1 UNION ALL
SELECT 4, 'Child3', 1, 3,       1 UNION ALL
SELECT 5, 'Child4', 1, 4,       1 UNION ALL
SELECT 6, 'MainA',  2, null,    1 UNION ALL
SELECT 7, 'ChildA', 2, 6,       1 UNION ALL
SELECT 8, 'ChildB', 2, 7,       1 UNION ALL
SELECT 9, 'Main!',  3, null,    1 


;WITH Projects 
(
    categoryid, 
    categoryname, 
    parentcategoryid
) 
AS
(
    -- Base Case
    SELECT  categoryid, 
            categoryname, 
            parentcategoryid 
    FROM @ProjectCategories 
    WHERE projectid = 1 -- Change this to 1, 2, or 3
        AND parentcategoryid IS NULL
    UNION ALL
    -- Recursive
    SELECT  pc.categoryid, 
            pc.categoryname, 
            pc.parentcategoryid 
    from Projects 
        INNER JOIN @ProjectCategories pc
            ON pc.parentcategoryid = Projects.categoryid  
)
SELECT * from Projects

Answer 3

SELECT
    COALESCE(parent.categoryid, child.categoryid),
    COALESCE(parent.categoryname, child.categoryname),
    COALESCE(parent.projectid, child.projectid),
    COALESCE(parent.parentcategoryid, child.parentcategoryid),
    COALESCE(parent.sortorder, child.sortorder)
FROM
    dbo.ProjectCategories child
    LEFT JOIN dbo.ProjectCategories parent
        ON child.parentcategoryid = parent.categoryid
WHERE
    child.ProjectId = @ProjectId 

COALESCE returns the first non-NULL parameter.

A somewhat safer version, in the case some of the columns of the parent category are NULL

SELECT
    CASE WHEN parent.categoryid IS NULL THEN child.categoryid ELSE parent.categoryid END,
    CASE WHEN parent.categoryid IS NULL THEN child.categoryname ELSE parent.categoryname END,
    CASE WHEN parent.categoryid IS NULL THEN child.projectid ELSE parent.projectid END,
    CASE WHEN parent.categoryid IS NULL THEN child.parentcategoryid ELSE parent.parentcategoryid END,
    CASE WHEN parent.categoryid IS NULL THEN child.sortorder ELSE parent.sortorder END
FROM
    dbo.ProjectCategories child
    LEFT JOIN dbo.ProjectCategories parent
        ON child.parentcategoryid = parent.categoryid
WHERE
    child.ProjectId = @ProjectId 

Answer 4

Should be fairly straightforward:

select categoryid,categoryname,projectid,parentcategoryid,sortorder 
  from dbo.ProjectCategories
 where parentcategoryid is null