CODEWITHSUNDEEP
sql-server-2005parent-childcommon-table-expression
rahularyansharma
rahularyansharma

Reputation: 10755

Parent row missing in child parent relationship in with CTE

i have a temporary table in which i have the following data , i want to filter the rows of child with his parent categoryID untill its reaches at the top of that Parent in those hierarchy .

Parent missing in Parent Child relationship

;with cte (rowid,ParentCategoryID,CategoryID,Status,Level,CategoryName,ISProduct) as
(
     Select  rowid,ParentCategoryID,CategoryID,Status,Level,CategoryName,ISProduct from #newtemp      where ParentCategoryId!=0

     union all
     select cte.rowid,cte.ParentCategoryID,cte.CategoryID,cte.Status,cte.Level,cte.CategoryName,cte.ISProduct
     from  #newtemp inner join cte ON cte.CategoryId=#newtemp.ParentCategoryId
)
select * from cte

Upvotes: 1

Views: 346

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You need replace cte.CategoryId=#newtemp.ParentCategoryId on c.ParentCategoryId = #newtemp.CategoryID

;with cte (rowid,ParentCategoryID,CategoryID,Status,Level,CategoryName,ISProduct) as
 (
  Select  rowid, ParentCategoryID, CategoryID, Status, Level, CategoryName, ISProduct 
  from #newtemp  
  where ParentCategoryId!=0
  union all
  select t.rowid, t.ParentCategoryID, t.CategoryID, t.Status, t.Level, t.CategoryName, t.ISProduct
  from  #newtemp t inner join cte c ON c.ParentCategoryId = t.CategoryID
  )
  select * from cte

Demo on SQLFiddle

Upvotes: 1

Arion
Arion

Reputation: 31239

If I understand you correct. You what something like this:

First some test data:

DECLARE @tbl TABLE
            (
                rowid INT,
                parentCategoryID INT,
                CategoryID INT,
                [Status] INT,
                [Level] INT, 
                CategoryName VARCHAR(100),
                ISProduct BIT
            )

INSERT INTO @tbl
SELECT 1,0,1,1,0,'jewellary',1 UNION ALL
SELECT 2,0,2,1,0,'f',0 UNION ALL
SELECT 11,2,4,1,10,'ghdf',1

Then the CTE like this:

;WITH cte_name (rowid,CategoryID,parentCategoryID,HasChildren)
AS
(
SELECT
    tbl.rowid,
    tbl.CategoryID,
    tbl.parentCategoryID,
    CASE WHEN EXISTS 
            (
                SELECT
                    NULL
                FROM
                    @tbl AS tblInner
                WHERE
                    tblInner.parentCategoryID=tbl.CategoryID
            )
        THEN 1
        ELSE 0
    END
    AS HasChildren
FROM
    @tbl AS tbl
WHERE
    tbl.parentCategoryID=0
UNION ALL
SELECT
    tbl.rowid,
    tbl.CategoryID,
    tbl.parentCategoryID,
    cte.HasChildren
FROM
    @tbl AS tbl
    JOIN cte_name AS cte
        on cte.CategoryID=tbl.parentCategoryID
)

SELECT 
    tbl.*
FROM 
    cte_name
    JOIN @tbl AS tbl
        ON cte_name.rowid=tbl.rowid
WHERE
    cte_name.HasChildren=1

Upvotes: 0

Related Questions