sqlsql-serversql-server-2008t-sqlreportserver

How to behave with GUID column in a self-reference table

There is a table named "Catalog" in ReportServer database tables. It has two column with type of GUID : "ItemID" and "ParentID" I want to build a query in SQL Server that can return a dataset which contain some records, in each record return ItemID, ParentID, PossibleChildIDs

for example assume that ItemID = firstGUID I want all the possible childs in hierarchy for firstGUID

Solution

  • we are in the same family if we have the same parent
    this is only one level of a hierarchy
    if a record can be both a child and a parent then you will need recursion 

    select family.* 
  from table as member 
  join table as familiy 
    on member.ParentID = family.ParentID 
 where member.ItemID = 'firstGUID'