0

Possible Duplicate:
Simplest way to do a recursive self-join in SQL Server?

I have to create a table in SQL that will comprise of groups of items/products. Each new group made will be made under one of the pre-defined groups or the groups previously formed. I want to keep all this data in a SQL Table. So far, I have though of creating a table like this:

  • Group ID
  • Group Name
  • Group Under (This will store the ID of the group under which this group is from

But this can only refer to just the next level, how will I get to know who is the super-parent of this group.

For example:

  • I have groups A, B, C.
  • A has further subgroups A1, A2, A3.
  • A1 has further subgroups, A11, A12, A13.

I will I have the information about super-parent group i.e A from A11 or A22 or A33?

Let me know if the problem is not clear..

Community
  • 1
  • 1
Samarth Agarwal
  • 2,044
  • 8
  • 39
  • 79
  • I don't need to have a join. I want to know which is the top level group in the chain for a particular group. – Samarth Agarwal Oct 30 '12 at 15:47
  • 1
    ***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... – marc_s Oct 30 '12 at 15:52
  • 3
    @SamarthAgarwal: You **do** need a JOIN. You need to recursively JOIN the Group table to itself, climbing the hierarchy of groups until `GroupUnder` is `NULL`. When it is `NULL`, you know that the `GroupID` in that row is the top level group. The post to which I linked does exactly that. This is not a trivial problem to solve. The solution may look daunting at first but really it's quite elegant. – Cᴏʀʏ Oct 30 '12 at 15:55
  • @Cory I am really thankful to you, but to be very honest, I am not getting it at all, maybe because I am new to this. I have to set up tax rates to top level groups, like 10% for A and 15 % to B. Now when I encounter a group called A12, I move up the hierarchy, and I reach A1 which I find is NOT a top level group! I again move up by one level and I find that I have reached A, which IS a top level group, I just fetch the tax rate from there and apply it to further calculations in my coding. – Samarth Agarwal Oct 30 '12 at 16:09
  • I am using SQL server 2008, C# for the programming, and VS 2010 as the IDE. – Samarth Agarwal Oct 30 '12 at 16:10
  • 1
    @SamarthAgarwal: See my answer below. I realize it's a bit contrived, but hopefully it points you in the right direction. – Cᴏʀʏ Oct 30 '12 at 16:21

1 Answers1

1

Assuming T-SQL and MSSQLServer (you didn't specify), and given that your Group table should look something like this:

Id | Name | ParentId
---+------+---------
 1 | A    | NULL
 2 | B    | NULL
 3 | C    | NULL
 4 | A1   | 1
 5 | A2   | 1
 6 | A3   | 1
 7 | A11  | 4
 8 | A12  | 4
 9 | A13  | 4

You can use the following recursive CTE to find the top level a given group, say 'A12':

WITH [Group](Id, Name, ParentId) AS
(
    SELECT 1, 'A'  , NULL UNION
    SELECT 2, 'B'  , NULL UNION
    SELECT 3, 'C'  , NULL UNION
    SELECT 4, 'A1' , 1    UNION
    SELECT 5, 'A2' , 1    UNION
    SELECT 6, 'A3' , 1    UNION
    SELECT 7, 'A11', 4    UNION
    SELECT 8, 'A12', 4    UNION
    SELECT 9, 'A13', 4

), q AS 
(
    SELECT  
        *
    FROM    
        [Group]
    WHERE
        [Name] = 'A12' -- Given 'A12' as the child
    UNION ALL
    SELECT  
        g.*
    FROM
        [Group] g
    JOIN    
        q
        ON      
        q.ParentId = g.Id
    )
SELECT  
    *
FROM
    q
WHERE
    ParentId IS NULL

This query returns:

Id | Name | ParentId
---+------+---------
 1 | A    | NULL
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • Can you please explain the statements a little so that I can adjust them in my _SQLDATAADAPTER_ query? I am unable to understand a few things. – Samarth Agarwal Oct 30 '12 at 16:28