SQL to get the parent table with a reference to itself

Question

I have a table like this:

Item { int ItemID int ParentID string Name } 

An element is actually a subset of the larger table. An object:

 Object { int ObjectID string Color } 

So ItemID is FK before ObjectID . Within an Item , a ParentID may refer to another element or to a parent.

What I would like to do is be able to iterate over everything that happens through my parents from the sheet in the Item relationship, until I finally can determine from which ObjectID given list of items is dropped.

I would like to do this in SQL. I am using SQL Server 2008.

That's what I think. I can just ParentID over the ParentID element until I can no longer join the ParentID with another element. This ParentID is the ObjectID I want to return.

I tried to get this to work using internal joins but no luck. I use C #, so if this can be done in linq and I don't suspect that this is possible without being terribly inefficient, that would be fine too.

Answer I went with:

 WITH ObjectH (ParentID, ItemID, Level) AS ( -- Base case SELECT ParentID, ItemID, 1 as Level FROM Item WHERE ItemID = @param UNION ALL -- Recursive step SELECT i.ParentID, i.ItemID, oh.Level + 1 AS Level FROM Item i INNER JOIN ObjectH oh ON c.ItemID = oh.ParentID ) SELECT TOP 1 ParentID FROM ObjectH ORDER BY Level DESC 

It works.

Answer 1

My attempt is to try to do this with a recursive SQL Server CTE.

Logically, this is how CTE works, and it should be, but I never used it with a circuit like yours.

Typically, the tree structure in SQL does not have an additional parent table called Object. (It looks like you are simulating the design of your objects in exactly the same way as 1: 1 in db? I would remake this template if I were you)

Try it and let me know if this works.

EDIT: slightly changed the underlying query condition

EDIT2: Changed the request to make it specific to a single item

 WITH ObjectHierarchy (ItemID, Name, ParentID, Level) AS ( SELECT ItemID, Name, ParentID, 1 as Level FROM Item it, Object ob WHERE it.ParentID = ob.ObjectID AND ob.ItemID = @itemIdToBeSearched UNION ALL SELECT i.ItemID, i.Name, i.ParentID, oh.Level + 1 AS Level FROM Item i INNER JOIN ObjectHierarchy oh ON i.ParentID = oh.ItemID AND oh.ItemID = @itemIdToBeSearched ) SELECT parentID FROM ObjectHierarchy WHERE LEVEL = 1 

Answer 2

First of all, this is a terrible design that allows the ParentID column to refer to two different tables as a "foreign key". You probably already know about this because of the difficulties you encounter when writing this request.

Having said that and assuming that you are at a point where you cannot redesign your circuit, I would decide to solve this problem by creating a view that represents Item as “should”, and then using the traditional recursive CTE to view the hierarchy.

 create view vwItem as select i.ItemID, case when o.ObjectID is null then i.ParentID else null end as ParentID, o.ObjectID, Name from Item i left join Object o on i.ParentID = o.ObjectID go ;with cteItemList as ( select i.ItemID, i.ParentID, i.ObjectID, i.Name, 1 as Level from vwItem i where i.ParentID is null union all select i.ItemID, i.ParentID, i.ObjectID, i.Name, il.Level+1 as Level from vwItem i inner join cteItemList il on i.ParentID = il.ItemID ) select * from cteItemList order by Level, ItemID 

Answer 3

Just create a scalar function in SQL that iterates for you, and include it in a view that mimics your table but adds the result of the function as a column. Then you base your LINQ object on the view, and you have the ParentID in the code.