tricky SQL query

Question

So I have this table, with 3 columns: ID (unique key), PositionID, and SupervisorID. PositionID and SupervisorID are both foreign keys to a Positions table.

What I haven't been able to figure out is a decently nice way of getting inherited subordinates. So for example:

---

ID PositionID SupervisorID

1     2           1

2     2           3

3     3           4

4     1           5
...

how could I select 2,3 and 4 as based on their subordinate position to 1. The current solution is quite a messy way of doing it, and it seems common enough of a problem that perhaps there is an accepted method.

Thanks.

Answer 1

You can do this to a certain depth with standard SQL (and probably any depth with various SQL enhancements) but the SQL statements will be hideous abominations from the depths of Satan's own hellpit.

SQL is a relational algebra, not something to be bent to a procedural model. Seriously, use code for that (even if it's a stored procedure or PL/SQL), it will make your SQL prettier, your DBAs and future maintainers happier and your life longer and more prosperous.

Answer 2

Recursive Common Table Expressions: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Answer 3

If you have an Microsoft SQL Server 2008 you should look at the HierarchyId datatype. I think it's exactly what you're looking for. You can do it without this type but then you'll have to listen to the other guys. ;)

Answer 4

I've written queries to do this exact thing and explode Bills of Material. I've used a query in a while loop that selects into a temp table and recursive common table expressions. As far as I know, those are the only way w/o using a recursive function/sproc - which is a no go in a production database (in my view) for a variety of reasons (firstly since sql server is restricted to 16 levels by default). I can post code if requested.

Answer 5

You could try:

 WITH HierarchyCTE (ID, PositionID, SupervisorID)
 AS
 (
   SELECT ID, PositionID, SupervisorID
     FROM SomeTable
     WHERE ID = 1
   UNION ALL
   SELECT b.ID, b.PositionID, b.SupervisorID
     FROM SomeTable AS b
  INNER JOIN HierarchyCTE AS c ON b.ID = c.PositionID 
 )
 SELECT ID, PositionID, SupervisorID FROM HierarchyCTE

or something close to that should work.