1

I have a cat table that has the following columns:

cat_id  | name            |   parent_id
1         cat1                1
2         subcat1             1
3         subcat1-subcat      2

This table has thousands of categories but that is the general structure.

When a user selects a top level category I have a query to get its children like this:

SELECT * FROM cat WHERE parent = $id

My problem is that I need to know if these children categories have children of their own.

I could do a loop on the results and do a query for each category returned, but I am hoping that there is a solution where I can use just one query, maybe it will require a sub query?

Thanks for the help.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Mike
  • 12,359
  • 17
  • 65
  • 86
  • You might be interested in my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) – Bill Karwin Nov 05 '11 at 22:57

2 Answers2

1
select *, (select COUNT(*) from cat c2 where c2.parent_id = c1.cat_id)  
from cat c1

The count column will be non-zero if there are sub-categories.

drdwilcox
  • 3,833
  • 17
  • 19
  • 1
    +1 Might be more efficient to do an `exists` subquery, to let the database know you're only interested in whether there are children, not the number of children – Andomar Nov 05 '11 at 22:22
1

You could use a subquery to check if there are child nodes:

select  *
,       case 
        when exists (select * from cat c2 where c2.parent_id = c1.cat_id) then 0
        else 1
        end as HasSubcategories
from    cat c1
Andomar
  • 232,371
  • 49
  • 380
  • 404