Return count of rows using recursive function.

• Jayraj.Todkar

  SSC Eights!

  Points: 829

  More actions

  December 19, 2008 at 2:39 am

  #199162

  Hi All,

  I have created a table as like parent Child structure. e.g (Topics and Reply).

  tblTopics (TopicID INT, ReplyID INT, ParentTopicID INT)

  I have another table say MisuseTopic.

  tblMisuseTopics (TopicID INT)

  I Want the count of replies of topic which are not misused.

  lets take one example.

  tblTopics

  TopicID ReplyID ParentID

  ~~~~~~ ~~~~~~~~ ~~~~~~~~

  700 NULL NULL

  701 700 700

  702 701 700

  703 700 700

  704 700 700

  tblMisuseTopic

  TopicID

  ~~~~~~

  701

  Here Total count of Replies is 4.

  but 701 is misused.

  and there is one reply against 701 reply which is 702.

  thats why 702 is also misused indirectly.

  so count should be 2.

  How can i get such type of count using recursive method?

  How can i implement this?

  i am trying this using Global variable and function. am i on right track?

  If any one knows the solution/ sugestion please let me know.

  Thanks

  Jayraj Todkar

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  December 19, 2008 at 5:16 am

  #915132

  This is all documented in Books Online, of course.

  DECLARE@tblTopics TABLE

  (

  TopicID INT,

  ReplyID INT,

  ParentID INT

  )

  INSERT@tblTopics

  SELECT700, NULL, NULL UNION ALL

  SELECT701, 700, 700 UNION ALL

  SELECT900, NULL, NULL UNION ALL

  SELECT702, 701, 700 UNION ALL

  SELECT703, 700, 700 UNION ALL

  SELECT800, NULL, NULL UNION ALL

  SELECT801, 800, 800 UNION ALL

  SELECT802, 801, 800 UNION ALL

  SELECT704, 700, 700

  DECLARE@tblMisuseTopic TABLE

  (

  TopicID INT

  )

  INSERT@tblMisuseTopic

  SELECT900 UNION ALL

  SELECT701 UNION ALL

  SELECT801

  ;WITH Yak (TopicID, ReplyID, ParentID)

  AS (

  SELECTt.TopicID,

  t.ReplyID,

  t.TopicID

  FROM@tblTopics AS t

  WHEREt.ParentID IS NULL

  AND NOT EXISTS (SELECT * FROM @tblMisuseTopic AS x WHERE x.TopicID = t.TopicID)

  UNION ALL

  SELECTt.TopicID,

  t.ReplyID,

  t.ParentID

  FROMYak AS y

  INNER JOIN@tblTopics AS t ON t.ReplyID = y.TopicID

  WHERENOT EXISTS (SELECT * FROM @tblMisuseTopic AS x WHERE x.TopicID = t.TopicID)

  )

  SELECTParentID AS TopicID,

  COUNT(ReplyID) AS Replies

  FROMYak

  GROUP BYParentID

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Jayraj.Todkar

  SSC Eights!

  Points: 829

  More actions

  December 19, 2008 at 5:45 am

  #915150

  Thanks Peso,

  this was an excellent solution. it worked straight away.

  once again Thanks.:)