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:
DivisionID Name Description Notes MasterDivisionID
---------- ---- ----------- ----- ----------------
0 Intivo - - NULL
1 Development - - 0
2 Marketing - - 0
3 Electronics - - 1
4 Mechanics - - 1
5 Electronics_1 - - 3
6 Electronics_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