Pull 3 tiers of related data from a single table

Question

Our business as a tiered Salesman relation, sometimes called an omni-tier. Its 3 deep.

in english: Salesman-A-tier has people under them, we'll call them salesman-B-tier, and b-tier has salesman under them salesman-C-tier.

table:

id, name, agentId 
1011, bob, 0
1012, jim, 1011
1013, tim, 1011
1014, sam, 1011
1015, dav, 1013
1016, kim, 1013
1017, sal, 1015
1018, vin, 1015

(the ID is the agents' Id, the field called agentId is that salesmans upstream agent)

what i need is a list of all the salesmen under (in this case bob or id=1011), 3 tiers deep.

i've gotten 2 levels deep but get throttled after that. figuring theres a better approach i cannot see myself, i'm asking for help.

my sql so far:

select c.id, c.name, c.agentId from salesmen s where s.agentId = 1011 or s.agentId = (select ss.agentId from salesmen ss where ss.id=s.agentid)

This gets me 2 tiers deep but i cannot get a third.

any help is appreciated. thanks in advance, Matthew

Answer 1

One easy option here would be to use a recursive CTE. Make sure you finish the previous statement with a semi-colon.

with recCTE as
(
/* Base case first */
SELECT 1 as theLevel, *
FROM theTable
WHERE AgentID = 0

/* Recurisve bit */
UNION ALL

SELECT r.theLevel + 1, t.*
FROM recCTE r
JOIN theTable t
ON r.ID = r.AgentID
)
SELECT *
FROM recCTE
WHERE theLevel <= 3;

Answer 2

I know that it's often a pain (or close to impossible) to rearchitect a table like this, but if that's an option then you should check out Joe Celko's book on trees and hierarchies in SQL. He has some alternative table designs, like the nested set model, which can make a query like yours trivial. Here's a brief example that I was able to find from Google.

Barring a redesign, if you're in MS SQL Server 2005 or greater then you can use CTEs as Rob suggests. I don't know what (if any) recursive functions other RDBMS's may offer.

Answer 3

A straight SQL-92 solution, avoiding both recursion (not universally implemented) and vendor-specific features (for the usual reasons):

select
    theAnswer.*
from
    salesmen s0
    join
    salesmen s1 on s0.id in (s1.id, s1.agentId)
    join
    salesmen theAnswer on s1.id = theAnswer.agentId
where
    0 = s0.agentId
    and
    1011 in (s0.id, s1.id)

The assumption here is that the salesperson of interest (id = 1011 in this case) may be either A-tier or B-tier. To restrict the query to searches starting at A-tier only, replace the last line with:

    1011 = s0.id

Another assumption is that there is exactly one row for each salesperson (id is UNIQUE), which implies that any given salesperson has a single agentId. If this is not so, replace the first line with:

select distinct

I should point out that there are four tiers in the sample data, not three as per the problem statement.

• A-tier: bob
• B-tier: jim, tim, sam
• C-tier: dav, kim
• D-tier: sal, vin

That being the case, the original query becomes:

select
    theAnswer.*
from
    salesmen s0
    join
    salesmen s1 on s0.id in (s1.id, s1.agentId)
    join
    salesmen s2 on s1.id in (s2.id, s2.agentId)
    join
    salesmen theAnswer on s2.id = theAnswer.agentId
where
    0 = s1.agentId
    and
    1011 in (s0.id, s1.id, s2.id)
    and
    (s0.id = s1.id or s1.id <> s2.id)

This last line's purpose is to avoid having to turn the query into a select distinct and is a simply a reduction of:

    (
        s0.id = s1.id and s1.id = s2.id
        or
        s0.id = s1.id and s1.id <> s2.id
        or
        s0.id <> s1.id and s1.id <> s2.id
    )

To get to a 5-level hierarchy, one need only compare the first and second queries to see the pattern emerge.

• The FROM clause gets an extra JOIN.
• The final JOIN must compare against this new JOIN.
• The second part of the WHERE clause gets an extra item.
• The last part of the WHERE clause builds on the unsimplified snippet above by adding an extra OR condition to handle doing the extra compares against the new JOIN, following the pattern made by the = and <> operators.

Answer 4

If you are using SQL Server 2008 or if you can use SQL Server 2008 - HierarchyId data-type in sql server 2008 can solve your problem very easily. Check these references below.

Reference 1

Reference 2

Here's a visual representation of how HierarchyId stores omni-tiered information.