Yet another SQL query question

[UCJefe]

I think the SQL portion of my brain is turned off today because I can't for the life of me figure out how to do this and I think it should be easy. I'll simplify the problem as much as possible. This is for MS SQL Server 2000 by the way.

Say I have 2 tables, Employee and DepartmentRelation whose schema (and some example data) looks something like the code attached.

DepartmentRelation.Emp_ID AND Employee.Parent_ID are both foreign keys to Employee.ID

Now say I want to get a list of all Employees AND their parents(!) for a given Dept_ID. So given Dept_ID = 1, I need to follow the foreign key to Employee.ID = 3 and then get that employee and all of his parents. In this case, I'd be expecting a result set with every record in Employee.

The Employee INNER JOIN DepartmentRelations ON DepartmentRelation.Emp_ID = Employee.ID is the easy part but I'm not sure how to get the records on up the line.

Any SQL experts out there or non-experts that have any idea how to do this.

 

[MrChad]

SELECT *
FROM Employee
WHERE EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = ID) OR
EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = Parent_ID)

 

[UCJefe]

Originally posted by: MrChad
SELECT *
FROM Employee
WHERE EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = ID) OR
EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = Parent_ID)

That gives me all the records in Employee whose ID or Parent_ID are present in the DepartmentRelation.Emp_ID field which is not what I want because most Employee.ID fields I am interested won't show up in DepartmentRelation. Basically DepartmentRelation.Emp_ID will reference "the lowest child employee" and then from there, I want to get all of that "lowest child employee's" parents in the same Employee table (it's a self-reference). It sounds like I might need a union or something but I'm at a loss. Thanks for the help, that might give me a different direction to go think about this.

 

[MrChad]

Originally posted by: UCJefe

Originally posted by: MrChad
SELECT *
FROM Employee
WHERE EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = ID) OR
EXISTS (SELECT 1 FROM DepartmentRelation WHERE DepartmentRelation.Emp_ID = Parent_ID)

That gives me all the records in Employee whose ID or Parent_ID are present in the DepartmentRelation.Emp_ID field which is not what I want because most Employee.ID fields I am interested won't show up in DepartmentRelation. Basically DepartmentRelation.Emp_ID will reference "the lowest child employee" and then from there, I want to get all of that "lowest child employee's" parents in the same Employee table (it's a self-reference). It sounds like I might need a union or something but I'm at a loss. Thanks for the help, that might give me a different direction to go think about this.

Ok, I understand your problem better now. Is there a limit to this hierarchy?

(SELECT Parent.*
FROM DepartmentRelation, Employee Child, Employee Parent
WHERE DepartmentRelation.Dept_ID = 1 AND
DepartmentRelation.Emp_ID = Child.ID AND
Child.Parent_ID = Parent.ID)
UNION
(SELECT Child.*
FROM DepartmentRelation, Employee Child
WHERE DepartmentRelation.Dept_ID = 1 AND
DepartmentRelation.Emp_ID = Child.ID)

 

[Cerebus451]

Originally posted by: MrChad
Ok, I understand your problem better now. Is there a limit to this hierarchy?

That would be my question. If 1 is a parent of 2 is a parent of 3 is a parent of 4 is a parent of 5 is a parent of 6 is a parent of 7 is a parent of 8 is a parent of 9 is a parent of 10, and 10 is the only record in the DepartmentRelation table, would you expect all 10 ids to come back? If so, I'm not sure there is a single SQL statement that would handle that. You would probably have to set up a stored procedure to handle the recursion.

 

[UCJefe]

Originally posted by: Cerebus451

Originally posted by: MrChad
Ok, I understand your problem better now. Is there a limit to this hierarchy?

That would be my question. If 1 is a parent of 2 is a parent of 3 is a parent of 4 is a parent of 5 is a parent of 6 is a parent of 7 is a parent of 8 is a parent of 9 is a parent of 10, and 10 is the only record in the DepartmentRelation table, would you expect all 10 ids to come back? If so, I'm not sure there is a single SQL statement that would handle that. You would probably have to set up a stored procedure to handle the recursion.

Yep, that is exactly what I expect and there is no hard limit to the number of parents although I could probably enforce something in the software which enters these records but then I could have a 10+ way UNION and that just seems ugly to me. MrChad, that's exactly what I wanted for 2 levels so I think that will work as a great base for a stored procedure.

If anyone else has any tricks for returning every record in a self-referencing foreign key relationship without knowing the number of levels you are going to have beforehand, let me know; otherwise I'll see if can put a stored proc together. It feels like you should be able to do that, but what do I know?

Thanks for all the help.

 

[Shazam]

A stack is perfect for what you need to do (n level recursion):

http://support.microsoft.com/d...?scid=kb;en-us;q248915

 

[UCJefe]

Originally posted by: Shazam
A stack is perfect for what you need to do (n level recursion):

http://support.microsoft.com/d...?scid=kb;en-us;q248915

Good article.

For future reference to anyone reading this topic, I found a great whitepaper here showing various methods of holding hierarchical information in a SQL Server database and different ways to query it out. The paper presented a UDF (User-defined function) to do exactly what I needed for the "adjancency list" type hierarchy which I was using (unknowingly). I have attached the UDF below (this only loosely corresponds to the schema I presented). I recommend anyone who is interested, to read the whitepaper referenced above.

Thanks everyone for your help.