SQL Query for a recursive relation (bad subject...)

  • Hello there!

    A question to all you out there.

    Let's say I've got a table called 'Person'. A person is part of a division, and therefore has a relation to a table called 'Division' where all divisions are stored.

    A division can have multiple sub-divisions. The 'Division' table has a primary key called DivisionID. There is also a recursive relation - a record in the 'Division' can be related to another record in the same table.

    Now to my simple? problem...

    Lets say I have a structure like below:

    1 root

    1.1 Division 1

    1.1.1 Subdivision 1 of Division 1

    1.1.2 Subdivision 2 of Division 1

    1.1.2.1 Subdivision 1 of SubDivision 2 of Division 1

    1.1.2.2 Subdivision 2 of SubDivision 2 of Division 1

    1.1.3 Subdivision 3 of Division 1

    1.2 Division 2

    1.2.1 Subdivision 1 of Division 2

    1.3 Division 3

    1.3.1 Subdivision 1 of Division 3

    1.3.2 Subdivision 2 of Division 3

    How does my SQL query look like if I would like to list all persons who is part of 1.1.1 and all of its sub-divisions?

    Something like:

    SELECT * FROM Person WHERE (DivisionID = 1 OR DivisionID = 4 OR DivisionID = 5 ... and so on for eternity....)

    How do I solve the fact that there might be a list of 400 different divisions? the WHERE part of my query would in that case be enourmously long...

    /Thanks


    Best Wishes,
    Stefan Johansson

  • Obviously, the solution depends on how the data is stored... As an example, if the hierarchical reference (1.1.2. etc.) is stored in the table Division, and you are sure that it will always reflect the actual status (meaning that if a department moves from one division to another, its reference will be updated accordingly), it is rather easy. To find all, who are member of 1.1.1 and its subdivisions, you can write:

    SELECT col1, col2, col3

    FROM Person p

    JOIN Division d ON d.divisionid = p.division

    WHERE d.reference LIKE '1.1.1.%' OR d.reference = '1.1.1'

    You can skip the "OR d.reference = '1.1.1'" if the last character is a dot (1.1.1. vs 1.1.1). If the dot isn't there, it is necessary, otherwise you get wrong results (direct members of 1.1.1 will be missing... and if you remove the dot from the LIKE comparision, 1.1.10 will be included).

    Otherwise, you have to pull the info by recursive joining, which is a lot more complicated. I've seen some nice solution of similar problem on these forums not long ago (=during last few months), unfortunately don't remember where to find it... It used UDF that is calling itself recursively. Try to search these forums, you should come up with something that will put you on the right track.

    HTH, Vladan

  • We had a similar problem and solved it the way Vladan described using the LIKE comparitor in the WHERE clause.

    Our biggest issue was maintaining the field when (in this example) division 1.1 was moved to division 2 and became division 2.1 this required updating of all of 1.1's sub divisions to be renamed as well.

    The fix looked like this we used 2 querries instead of an "or" to simplify the update statements:

    UPDATE Division SET reference = '2.1'  +  RIGHT(reference, LEN(reference) - LEN('2.1')) WHERE reference like LIKE '1.1.%'

    UPDATE Division SET reference = '2.1'  WHERE reference = '1.1'

  • Here is what I came up with. Basically if you have a recursive relationship, you have to walk down it. You can use a cursor or a while loop like I did here. You could also move this code to a function if you wanted.

    SET NOCOUNT ON

    DECLARE @Division TABLE

    (

    div_id int,

    div_name varchar(100),

    parent_div_id int NULL

    )

    INSERT @Division

    SELECT 1, 'Root', NULL UNION

    SELECT 2, 'Division A', 1 UNION

    SELECT 3, 'Subdivision A of Division A', 2 UNION

    SELECT 4, 'Subdivision B of Division A', 2 UNION

    SELECT 5, 'Subdivision A of Subdivision B of Division A', 4 UNION

    SELECT 6, 'Subdivision B of Subdivision B of Division A', 4 UNION

    SELECT 7, 'Subdivision C of Division A', 2 UNION

    SELECT 8, 'Division B', 1 UNION

    SELECT 9, 'Subdivision A of Division B', 8 UNION

    SELECT 10, 'Division C', 1 UNION

    SELECT 11, 'Subdivision A of Division C', 10 UNION

    SELECT 12, 'Subdivision B of Division C', 10

    declare @StartingDivision int

    set @StartingDivision = 2

    declare @DivisionsSelected table (div_id int)

    insert into @DivisionsSelected select @StartingDivision

    while (@@rowcount > 0)

    begin

    insert into @DivisionsSelected

    select b.div_id

    from @division a inner join @division b on a.div_id = b.parent_div_id

    where a.div_id in (select div_id from @DivisionsSelected)

    and not exists (select 1 from @DivisionsSelected c where c.div_id = b.div_id)

    end

    select * from @DivisionsSelected

    /* Now you can use this @DivisionsSelected table in an in clause or however you want to query the person table. I didn't create the person table, but it would look something like this. */

    SELECT * FROM Person WHERE DivisionID in (select div_id from @DivisionsSelected)

  • Hi again!

    Thanks for all the replys guys! really appreciated!

    However, unfortunate I wasn't very clear about my problem. It's not that advanced...

    The "structure" part should have been more described... sorry.

    My 'Person' table:

    Person

    ------

    PersonID (PK)

    Firstname

    Lastname

    ...

    ...

    DivisionID

    My 'Division' table:

    Division

    --------

    DivisionID (PK)

    Name

    Description

    Notes

    MasterDivisionID (Null for the root division, otherwise the "Master" divisions' DivisionID)

    Lets say there are a total of five divisions:

    DivisionIDNameDescriptionNotesMasterDivisionID

    ----------------------------------------------

    0Intivo--NULL

    1Development--0

    2Marketing--0

    3Electronics--1

    4Mechanics--1

    5Electronics_1--3

    6Electronics_2--3

    A person can be part of any of the divisions above.

    Now lets say I would like to list all persons who is part of "Development" and all its sub-divisions, that is

    all persons with DivisionID = 1, 3, 4, 5 or 6.

    Another scenario is to list all persons who is part of "Electronics" and all its sub-division, that is

    all persons with DivisionID = 3, 5 or 6.

    How do I solve this??

    /Many thanks!


    Best Wishes,
    Stefan Johansson

  • The script I posted does what you are wanting. Is there some reason it won't work?

    Chris

  • Oops my mistake. Sorry Chris.

    Don't know why I... hmmm missed your code

    I'm not sitting at my "work" computer right now, so I can't verify that it's working, but it seem to do just what I want!

    Thank you very much!! 10 points for you

    /


    Best Wishes,
    Stefan Johansson

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply