TSQL A problem with categories tree

I have a problem with recursive CTE query

Let's say that I have that category tree (Category table)

In my CTE query, I search for all children of the 1 category: (that query works fine)

    with mq as
    (
        select c.Id as parent, c.Id as child 
            from dbo.Category c 
            where c.Id = 1
              union all
                select q.child, c.Id
                from mq q
                    inner join dbo.Category c on q.child = c.IdParentCategory
    )

The output

Then, I want to get that Category ID, wchih doesn't have a child: categories 9,10,12,14,15

with mq as
    (
        select c.Id as parent, c.Id as child 
        from dbo.Category c 
        where c.Id = 1
            union all
                select q.child, c.Id
                from mq q
                    inner join dbo.Category c on q.child = c.IdParentCategory
                where child in 
                (
                    select c1.Id
                    from  dbo.Category c1
                    where not exists(select c2.Id 
                                     from dbo.Category c2 
                                     where c2.Id = c1.IdParentCategory)
                ) 
    )

but the output is wrong:

why ? Any ideas will be helpful !

if I separate the query from CTE, everything is OK

declare @tab table
(parent int, child int);

insert into @tab
        select * from mq

    delete from @tab
    where child  in (
        select c1.parent
        from @tab c1
        where not exists(select c2.parent from @tab c2 where c2.parent = c1.child)
    )

Answers


with mq as
(
    select c.Id as parent, c.Id as child 
        from dbo.Category c 
        where c.Id = 1
          union all
            select q.child, c.Id
            from mq q
                inner join dbo.Category c on q.child = c.IdParentCategory
)
select child from mq where child not in (select parent from mq)

Would seem to give the output you want - in fact your description of the problem almost took this form.


Need Your Help

Insert Multiple Rows Into Temp Table With SQL Server 2012

sql sql-server-2012 bulkinsert

These StackOverflow questions here, here, and here all say the same thing, but I can't get it to run in SSMS or SQLFiddle

Can someone help me figure out why my app is crashing when running on iPad. Culprit is unsupported framework - iAd

iphone objective-c cocoa-touch ipad sdk

I have read plenty of questions and answers here about weak linking, checking if a class is valid before executing code etc. My app is basically finished and ready (at least I thought it was, my f...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.