Question How do I exclude indirectly related records?

[keb1965]

I have a table that has a self-referencing FK. The danger in this is that it is entirely possible to create a circular reference if care is not exercised during INSERT and UPDATE.

For ease of discussion, lets say tblItems has columns ThisID and ParentID. ParentID may be NULL and is the FK, ThisID is the PK and identity.

When I select from this table I am looking for all records that don't have a parentID set, however, there comes a time when one or more items is the top level parent and retrieving its parent is not possible.

This returns all the possible records that need a parent:

SELECT * FROM tblItems WHERE ParentID IS NULL

Obviously a record cannot be its own parent and a record cannot be a child to one of its children and on down the line.

So, given the previous select, how can I ensure that the items selected are not found in the ParentID column of any selected record's parent, grandparent, etc.

I had considered setting the ParentID value to -1 if it is a top level item so that I could differentiate it from items without a parent, however, it is entirely possible that an item that has children can be made the child of another parent item.

 

[keb1965]

I managed to create a workable solution, but I am not sure it is the best. To resolve the issue, I created a function that returns whether or not an item is in the parental lineage of an item.

CREATE FUNCTION [dbo].[IsNodeInParentLineageOfChild]
(
	@ChildID As Int,
	@ParentID As Int
)
RETURNS BIT
AS
BEGIN
	DECLARE @Success BIT
	DECLARE @PID Int
	SET @PID = @ParentID

	WHILE (@PID != @ChildID AND @PID IS NOT NULL)
		BEGIN
			SET @PID = (SELECT TOP 1 PID FROM tblItems WHERE ID = @PID)
		END
		
	IF (@PID IS NULL)
		BEGIN
			SET @Success = 0
		END
	IF (@PID = @ChildID)
		BEGIN
			SET @Success = 1
		END

	RETURN @Success

Then in my select I use this:

	SELECT * FROM tblItems

	WHERE PID IS NULL
	AND dbo.IsNodeInParentLineageOfChild(ID, @ThisID) = 0

So for each row, it checks to see if the value of IsNodeInParentLineageOfChild is 0 (meaning it is not) and then adds it based on that result.

Sometimes it helps just to spell it out on a forum ...

Is there any problems that jump out and say "FIX ME!"