[SQL] Overzicht van een afdeling en onderafdeling

[TweakersOnly]

Info vooraf:

Een tabel AFDELING is als volgt gedefinieerd:

int AfdelingID //Unieke sleutel
int Omschrijving
int ParentID //Sleutel van de bovenliggende afdeling

De ParentID van een afdeling hoeft dus niet altijd ingevuld staan, de directie hoeft zich tegenover niemand te verantwoorden.

Middels SQL wil ik door een input van een afdelingID de gegevens van het desbetreffende afdeling EN de onderliggende afdeling terugkrijgen. Zelf heb ik de volgende query:

code:

SELECT * FROM Afdeling WHERE AfdelingID = 1 OR ParentID = 1

Probleem is dat ik alleen de eerste onderliggende laag te zien krijg. Bijvoorbeeld:
Afdeling1 is hoofd van Afdeling2
Afdeling2 is hoofd van Afdeling3
Afdeling3 is hoofd van Afdeling4
In mijn voorbeeld krijg ik door de query alleen Afdeling1 en Afdeling2 terug. Echter: Afdeling1 is indirect ook hoofd van Afdeling3 en Afdeling4. Hoe kan ik deze informatie terugkrijgen?

[rickmans]

SELECT * FROM Afdeling WHERE AfdelingID = 1 OR (ParentID = 1 OR ParentID = 2 OR ParentID = 3) volgens mij kan zo

[Dash2in1]

nogal wiedes, maar erg algemeen is dat niet.

[TweakersOnly]

rickmans schreef op 29 augustus 2002 @ 08:57:
SELECT * FROM Afdeling WHERE AfdelingID = 1 OR (ParentID = 1 OR ParentID = 2 OR ParentID = 3) volgens mij kan zo

Voor dit voorbeeld zou jouw query wel gelden, maar ik wil een algemene query maken zonder dat ik weet hoeveel afdelingen binnen een bedrijf zijn geregistreerd.

[rickmans]

TweakersOnly schreef op 29 augustus 2002 @ 09:00:
[...]

Voor dit voorbeeld zou jouw query wel gelden, maar ik wil een algemene query maken zonder dat ik weet hoeveel afdelingen binnen een bedrijf zijn geregistreerd.

Ik denk dat je dan kan werken met een loopje en zo

[cameodski]

Op basis van je huidige tabel is dit niet met één SQL statement op te lossen. Je zult in dit geval recursie moeten toepassen.
Een alternatief is om een extra veld op te nemen in de tabel waarin je info over de bovenliggende afdelingen opneemt.

Even een voorbeelde ter verduidelijking:

Afd 1: 1||
Afd 2: 1||2||
Afd 3: 1||2||3||
Afd 4: 1||2||4||

Als je nu afdeling 1 met alle onderliggende niveau's wilt hebben hoef je dus alleen maar de volgende filter toe te voegen: WHERE (column_name LIKE '1||%')

[Verwijderd]

2 manieren:

1) via nested sets. Ene meneer Joe Celko heeft hier een boek over geschreven en een basic idee omtrent nested sets is hier te vinden:
http://groups.google.com/...posting.google.com&rnum=3

zoek ook op google omtrent CELKO en 'nested sets'.

2) via precalculated parent-child sets. Dit is een methode die ikzelf heb bedacht (maar anderen zullen dat ongetwijfeld ook hebben gedaan) en die is gebaseerd op het feit dat je op het moment dat je een relatie toevoegd aan je parent-child table, je ook de hierarchie weet, en die dus kunt opslaan. Vroeger, toen ik nog hobbiede , programmeerde ik veel grafische demos en keyword in de demoscene was: "precalc". Aldus doe je hier ook:

Je maakt een aparte tabel waar je alle parents van een zekere child opslaat:
ChildID, int
PossibleParentIDInPath, int

Heb je bv:

child	parent
1	NULL
2	1
3	1
4	2
5	3
6	2

dan sla je in je precalc table op:

ChildID	PossibleParentIDInPath
1	0
2	0
2	1
3	0
3	1
4	2
4	1
4	0
5	3
5	1
5	0
6	2
6	1
6	0

Hier kun je een 1 shot query op loslaten, die meteen je hierarchie ophoest. Zodra de hierarchie WIJZIGT, muteer je ook je precalc tabel. Dit doe je in een storedprocedure die in een serialized transaction de tabel muteert. (dus geen andere transactions mogen op dat moment de tabel muteren).

Beide oplossingen hebben nadelen, het is aan jou om af te wegen welke nadelen voor jou het zwaarst wegen (optie 1) heeft als nadeel dat updates van de hierarchie met veel nodes erg lang kan duren).

Wat Celko goed doorheeft is dat SQL een setbased language is, en jouw structuur, hoe common ook, zich niet leent voor een set-based benadering.