Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Performing a hierarchical query...

    Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.

    Code:
    ID       ParentID       Name         CreateDate
    358      2              SMITH, JOHN  3/3/2008 11:15:23 am
    359      358            Invoice      3/5/2008 4:13:52 pm
    360      358            Shipping     3/5/2008 5:11:09 pm
    361      358            Receiving    3/6/2008 4:22:01 am
    The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps this might help: Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    That's perfect! Thanks a bunch!

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Recursive CTE

    You should definitely check out "recursive CTEs" (common table expressions). It is an amazing and brilliant implementation of recursive SQL logic.

    Here is a book if you are interested:
    SQL Server 2005
    Stored Procedure Programming in T-SQL & .NET
    by Dejan Sunderic, MCDBA

    See pages 234 to 237.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •