Does SQL Server 2000 support hierarchial queries?

[Smorton]

Oracle has the connect by and start with commands. Does SQL Server 2000 have the ability to query tables with a hierarchial relationship like Oracle does?

Thanks

 

[sguslan]

Hi ,,
I am not sure if it does within an SQL statement, but a quick search through BOL, showed an example in TranscatSQL. Search SQL Books Online for "hierarchical relation" and read the topic titled "Transact-SQL Tips" ...

Good luck ..

Salim

 

[mowog74]

I'm not sure what you're using for a front end, but if it can use OLEDB you can use the MSDataShape provider to return a hierarchical recordset, in which certain fields in the parent recordset are actually sub-recordsets themselves. Here's a VB/ADO example from the MSDN help that uses the pubs database:

Sub datashape()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rsChapter As Variant

    cnn.Provider = "MSDataShape"
    cnn.Open    "Data Provider=MSDASQL;" & _
               "DSN=vfox;uid=sa;pwd=vfox;database=pubs”
'STEP 1
    rst.StayInSync = FALSE
    rst.Open    "SHAPE  {select * from authors} 
               APPEND ({select * from titleauthor} AS chapter 
               RELATE au_id TO au_id)", 
               cnn
'STEP 2
    While Not rst.EOF
        Debug.Print    rst("au_fname"), rst("au_lname"), 
                     rst("state"), rst("au_id")
'STEP 3
        Set rsChapter = rst("chapter")
'STEP 4
        While Not rsChapter.EOF
            Debug.Print rsChapter(0), rsChapter(1), 
                        rsChapter(2), rsChapter(3)
            rsChapter.MoveNext
        Wend
        rst.MoveNext
    Wend
End Sub

In this case, the misnamed field "chapter" in the main recordset is actually a set of titles for each author.