0

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

Alex
  • 21,273
  • 10
  • 61
  • 73
Mohsen
  • 196
  • 3
  • 15
  • 3
    Can you not use a [recursive query](http://stackoverflow.com/questions/25289976/finding-all-children-in-a-heirarchy-sql)? – Clockwork-Muse Dec 03 '14 at 11:35
  • @bummi it is ok to use recursive query but i dont know how – Mohsen Dec 03 '14 at 11:39
  • Google recursive SQL query. Basically for every row you select, you need to then read every other row in the table to find rows where the parent GUID matches the current item GUID. @Clockwork-Muse linked you to an example. – Inspector Squirrel Dec 03 '14 at 11:44
  • @Clockwork-Muse sorry i read some link about recursive query here but all of them have type of int column, but not GUID – Mohsen Dec 03 '14 at 11:54
  • @Clockwork-Muse i'm getting confused – Mohsen Dec 03 '14 at 11:54
  • Datatype doesn't matter, [the technique still works just fine](http://sqlfiddle.com/#!3/d891e/1/0). – Clockwork-Muse Dec 03 '14 at 12:09
  • @Clockwork-Muse my dear friend i want to find all possible children for each record, not parent for a single record – Mohsen Dec 03 '14 at 13:09
  • But that link does find children. A possible problem is that you can only search on the parent. You need to be more explicit with what you need and a data example. – paparazzo Dec 03 '14 at 17:06

1 Answers1

1

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'
paparazzo
  • 44,497
  • 23
  • 105
  • 176