CODEWITHSUNDEEP
sql-servert-sql
Rod
Rod

Reputation: 15475

Get the sales rep and where they belong

    TerritoryId, Description, ParentTerritoryId, Type 
--------------------------------------------------------
    1, UnitedStates, null, Territory 
    1, John Smith, 1, Sales Rep 
    2, Georgia, 1, Territory
    2, Jane Doe, 2, Sales Rep 
    2, Ann Smith, 2, Sales Rep

How can I write the following T-SQL? Let's say I'm searching for a name like "Ann Smith".

I'd like the resultset to look like:

1, United States, null, Territory
2, Georgia, 1, Territory
2, Ann Smith, 2, Sales Rep

Basically, I want to find a sales rep or sales reps and what organization they belong to all the way up the chain.

Upvotes: 2

Views: 458

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Assuming SQL Server 2005+ so we can use a recursive CTE and assuming the TerritoryId values should really be unique:

TerritoryId  Description   ParentTerritoryId  Type 
--------------------------------------------------------
1            UnitedStates  NULL               Territory 
2            John Smith    1                  Sales Rep 
3            Georgia       1                  Territory
4            Jane Doe      3                  Sales Rep 
5            Ann Smith     3                  Sales Rep

Then you can do this:

WITH cteRecursion AS (
    SELECT TerritoryId, Description, ParentTerritoryId, Type, 1 AS Level
        FROM YourTable 
        WHERE Description = 'Ann Smith'
    UNION ALL
    SELECT t.TerritoryId, t.Description, t.ParentTerritoryId, t.Type, c.Level + 1
        FROM YourTable t
            INNER JOIN cteRecursion c
                ON t.TerritoryId = c.ParentTerritoryId
)
SELECT TerritoryId, Description, ParentTerritoryId, Type 
    FROM cteRecursion 
    ORDER BY Level DESC;

Upvotes: 2

Last Rose Studios
Last Rose Studios

Reputation: 2481

While it is possible to have a multilevel structure in the table ideally you would want to split your table.

one table for territory and one for sales reps.

If your sales reps can have more than one territory you would need to go to 3 tables one for sales reps, one for territory, and a lookup table.

if you are going to do a multi level table you need each entry to have a unique id

Upvotes: 0

Related Questions