Recursion in SQL query
2 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Friday, October 24, 2003 04:36 AM

I´m trying to make af discussion forum in a mySQL database. In the discussion forum there can be created many debat topics. Each debat topic ( the records ID is called debatID in the table ) can have many debat comments. The table with debat comments ( the records ID is called debatItemID ) is shown below. One debat comment can have many related debat comments ( many sons, that´s why I have made the record debatItemOwnerID ). Each son ( debat comment ) is also called a debat comment, which also can have many debat comments. Actually it is a tree-structure. The problem: I want to count how many debat comments there are related to a main debat comment ( the number of sons to the debat comment which doesn´t have a father( also called debatItemOwnerI   More>>

I´m trying to make af discussion forum in a mySQL database. In the discussion forum there can be created many debat topics. Each debat topic ( the records ID is called debatID in the table ) can have many debat comments. The table with debat comments ( the records ID is called debatItemID ) is shown below. One debat comment can have many related debat comments ( many sons, that´s why I have made the record debatItemOwnerID ). Each son ( debat comment ) is also called a debat comment, which also can have many debat comments. Actually it is a tree-structure.


The problem: I want to count how many debat comments there are related to a main debat comment ( the number of sons to the debat comment which doesn´t have a father( also called debatItemOwnerID ).


Here is an example of a query example:



Java ( debat topic )


- What is JAVA? ( debatItemSubject ) ( 10 debat comments )


- JAVA vs. C++( debatItemSubject ) ( 25 debat comments )



The table ( debatItem ) is designed like this:


debatItemID ( PRIMARY KEY, auto increment id )


debatItemSubject ( Subject to debat comment )


debatItemText ( Text to debat comment )


debatItemOwnerID ( Father debat comment, f.ex. if the father is What is JAVA?, the the son is RE: What is JAVA? )


debatID ( SECONDARY KEY, debat topic )


compID ( SECONDARY KEY, companyID )

   <<Less

Re: Recursion in SQL query

Posted By:   WarnerJan_Veldhuis  
Posted On:   Friday, October 24, 2003 06:27 AM

You should expiriment with GROUP BY



select debatItemOwnerId, count(debatItemID) as total from debatitem group by debatItemOwnerId, debatItemID where debatItemOwnerId is null


sort of... couldn't test it, but you get the idea....

Re: Recursion in SQL query

Posted By:   Stephen_McConnell  
Posted On:   Friday, October 24, 2003 05:16 AM

SELECT COUNT(*) FROM debatItem WHERE debatID =


I think that should do it... no need for recursion.


Stephen McConnell

About | Sitemap | Contact