[SQL] Query op hiërarchische tabel

[Verwijderd]

Stel ik heb een tabel waarin een aantal landen, provincies, plaatsen, stadsdelen, etc. instaan. Het aantal niveau's is niet van te voren bekend, en kan onbeperkt zijn.

Deze tabel heet Regions en ziet er ongeveer zo uit:
Regions
- RegionId
- Name
- ParentId

Waarbij ParentId dus naar het RegionId van de ouder region verwijst. Als dat 0 is, dan gaat het om een root item.

Hiermee is dus een boom te maken die er ongeveer zo uit ziet:

code:

Nederland
| Noord Holland
| | Amsterdam
| | | Noord
| | | Zuid
| Utrecht
| | Amersfoort
| | | Nogwat
| | Etcetera

Dit levert geen enkel probleem op , maar wat wel een probleem oplevert is het queriën van deze data. Stel bijvoorbeeld dat ik wil zoeken naar alle regio's die met 'Am' beginnen. Het resultaat moet nu Amsterdam, Noord, Zuid, Amersfoort en Nogwat zijn.

Maar hoe kan ik zo'n query opbouwen

Nu doe ik het heel omslachtig door at run-time de diepte van de boom te bepalen en dan een hele ingewikkelde SQL string samen te stellen met allerlei "IN (SELECT ...) OR IN (SELECT ...) ... " contrsucties. Dit is zeer onduideliljk en moeilijk te onderhouden.

Ik zat eraan te denken om een veld Parentage toe te voegen waarin dan de hierarchische string staat die aangeeft waar de region in de tree zit, dus bv. "1.12.245".
Dan wordt het probleem al makkelijker door eerst "SELECT Parentage FROM Regions WHERE Name LIKE 'Am%'" te doen, en dan in een loopje een nieuwe query op te bouwen die er uitziet als "SELECT * FROM Regions WHERE Parentage LIKE '{parentage van region 1}%' OR Parentage LIKE '{parentage van region 1}%' OR etc.."
Maar dan nog kan het niet in één query .

Nu is m'n vraag heel simpel : Kán het in één query, en zo ja, hoe?

[cameodski]

Een vergelijkbaar probleem is kort geleden hier nog aan de orde geweest. Misschien dat daar je antwoord al bij staat:
[rml][ SQL] Overzicht van een afdeling en onderafdeling[/rml]

[Verwijderd]

Die heb ik gemist, sorry dat het dubbel is...

Maar ik schiet er niks mee op, want ook daar stond geen (makkelijke) oplossing. Ik zal wel iets met Parentage gaan knutselen, want dat lijkt me het makkelijkst, mischien is het mogelijk met wat groupings...

Een goede link is trouwens ook: http://www.webgoeroe.net/item/277. Daar staan een aantal oplossingen genoemd, die helaas allen niet goed (genoeg) werken.

[Verwijderd]

KoenM,

Ik weet niet of de volgorde van je resultset ook nog belangrijk is, maar zo niet is dit volgens mij de oplossing:

code:

SELECT Naam
FROM   Regions
WHERE  Naam LIKE 'Am%'

UNION

SELECT Naam
FROM   Regions
WHERE  ParentID IN
(SELECT RegionID
 FROM   Regions
 WHERE  Naam LIKE 'Am%')

[cameodski]

Verwijderd schreef op 04 september 2002 @ 20:46:
Die heb ik gemist, sorry dat het dubbel is...

Maar ik schiet er niks mee op, want ook daar stond geen (makkelijke) oplossing. Ik zal wel iets met Parentage gaan knutselen, want dat lijkt me het makkelijkst, mischien is het mogelijk met wat groupings...

Een goede link is trouwens ook: http://www.webgoeroe.net/item/277. Daar staan een aantal oplossingen genoemd, die helaas allen niet goed (genoeg) werken.

Dubbel: tja je kunt niet alles bijhouden

Een moeilijk probleem heeft vaak ook een ingewikkelde oplossing. Je wil SQL 'misbruiken' voor een recursie probleem. Logisch dat je dan rare dingen moet gaan doen.

groupings?? Als je GROUP BY bedoelt dan geef ik je weinig kans.

Verwijderd schreef op 05 september 2002 @ 07:51:
KoenM,

Ik weet niet of de volgorde van je resultset ook nog belangrijk is, maar zo niet is dit volgens mij de oplossing:

code:

SELECT Naam
FROM   Regions
WHERE  Naam LIKE 'Am%'

UNION

SELECT Naam
FROM   Regions
WHERE  ParentID IN
(SELECT RegionID
 FROM   Regions
 WHERE  Naam LIKE 'Am%')

Deze oplossing werkt alleen maar als het aantal niveau's vast ligt en beperkt is. Als dat niet het geval is, dan is dit niet zo'n goede oplossing.

[sverzijl]

Als het een Oracle RDBMS betreft kan je hiervoor de CONNECT BY clause voor gebruiken.

[cameodski]

sverzijl schreef op 05 september 2002 @ 09:01:
Als het een Oracle RDBMS betreft kan je hiervoor de CONNECT BY clause voor gebruiken.

Mmm, binnenkort maar eens gaan kijken wat die clause dan wel precies inhoudt.
Wel vreemd dat ze middels zo'n simpele clause opeens het recursie probleem kunnen oplossen.

[Verwijderd]

cameodski schreef op 05 september 2002 @ 08:49:
[...]
Een moeilijk probleem heeft vaak ook een ingewikkelde oplossing. Je wil SQL 'misbruiken' voor een recursie probleem. Logisch dat je dan rare dingen moet gaan doen.

Klopt, ik heb het ook al opgegeven. Op zich is het recursiefe niet het probleem want door een soort Parentage veld is dat heel makkelijk op te lossen.

Het probleem is dat er meerdere resultaten kunnen zijn, en dat je dan een (onbekend) aantal deel bomen moet mergen. Maar goed, ik ga wel wat hacken .

[FastWallie]

Oracle kent :
--------
SELECT LPAD(' ', 2*(LEVEL-1)) || ename org_chart, empno, mgr, job
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
-------
Erg handig

zie :http://dblab.changwon.ac.kr/oracle/sqltest/hierarchical.html

[sverzijl]

cameodski schreef op 05 september 2002 @ 12:02:
[...]

Mmm, binnenkort maar eens gaan kijken wat die clause dan wel precies inhoudt.
Wel vreemd dat ze middels zo'n simpele clause opeens het recursie probleem kunnen oplossen.

Waarom is dat vreemd ?
Overigens kent deze clause aardig wat beperkingen, maar voor 'simpele' problemen als hierboven werkt het prima.

[Verwijderd]

Als je SQL-Server gebruikt kan je het beste gebruik maken van Stored Procedures om je gegevens op te halen. Dit is in eerste instantie al veel sneller dan elke keer een query door te geven, want die wordt elke gecontroleerd op fouten en een bij een Stored Procedure is dit maar één keer bij de creatie en niet meer bij de uitvoering ervan.

Ik heb volgende 2 Stored Procedures aangemaakt om jou probleem op te lossen.
Hierin maak ik gebruik van een tijdelijke tabel, om tussentijds de gevonden Regions in te stoppen.

Dit is de eerste:

ALTER PROCEDURE GetRegionsList
@LikeName varchar(50)
AS
DECLARE @RegionID int,
@Name varchar(50),
@ParentID int
BEGIN
CREATE TABLE #TempRegions
(TempRegionID int,
TempName varchar(50),
TempParentID int)

DECLARE cRegions CURSOR FOR
SELECT RegionID,
Name,
ParentID
FROM REGIONS
WHERE Name LIKE @LikeName

OPEN cRegions

FETCH NEXT FROM cRegions
INTO @RegionID,
@Name,
@ParentID

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempRegions
VALUES (@RegionID, @Name, @ParentID)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC GetSubRegions @RegionID

SET NOCOUNT OFF
SET ANSI_WARNINGS ON

FETCH NEXT FROM cRegions
INTO @RegionID,
@Name,
@ParentID
END

CLOSE cRegions
DEALLOCATE cRegions

SELECT TempRegionID,
TempName,
TempParentID
FROM #TempRegions

DROP TABLE #TempRegions
END

Dit is de tweede die door de eerste wordt opgeroepen:

ALTER PROCEDURE GetSubRegions
@RegionParentID int
AS
BEGIN
INSERT INTO #TempRegions
SELECT RegionID, Name, ParentID
FROM Regions
WHERE ParentID = @RegionParentID

WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO #TempRegions
SELECT RegionID, Name, ParentID
FROM Regions
WHERE ParentID IN
(SELECT TempRegionID
FROM #TempRegions
WHERE TempRegionID NOT IN
(SELECT TempParentID
FROM #TempRegions))
END
END

Een stukje van de code hierin wordt herhaald zolang als er onderliggende niveaus blijven gevonden.

Hopenlijk is dit een oplossing voor je probleem

[cameodski]

Verwijderd schreef op 06 september 2002 @ 11:17:
Als je SQL-Server gebruikt kan je het beste gebruik maken van Stored Procedures om je gegevens op te halen. Dit is in eerste instantie al veel sneller dan elke keer een query door te geven, want die wordt elke gecontroleerd op fouten en een bij een Stored Procedure is dit maar één keer bij de creatie en niet meer bij de uitvoering ervan.

En als het SQL Server 2000 is, kun je beter user defined functions gebruiken, want dan kun je een oneindig aantal niveau's diep gaan.
Ook kun je dan gebruik maken van het datatype table ipv temporary tables. Dat scheelt weer een stukje performance.

Probleem blijft alleen nog steeds dat je met cursors en loops zit te werken en dat is nu juist wat je eigenlijk niet wil.

[Verwijderd]

Verwijderd schreef op 06 september 2002 @ 11:17:
Als je SQL-Server gebruikt kan je het beste gebruik maken van Stored Procedures om je gegevens op te halen. Dit is in eerste instantie al veel sneller dan elke keer een query door te geven, want die wordt elke gecontroleerd op fouten en een bij een Stored Procedure is dit maar één keer bij de creatie en niet meer bij de uitvoering ervan.

Ik heb volgende 2 Stored Procedures aangemaakt om jou probleem op te lossen.
Hierin maak ik gebruik van een tijdelijke tabel, om tussentijds de gevonden Regions in te stoppen.

[VEEL CODE]

Een stukje van de code hierin wordt herhaald zolang als er onderliggende niveaus blijven gevonden.

Hopenlijk is dit een oplossing voor je probleem

Tanx ! Maar helaas moet het ook op Access draaien, dus zijn SP's geen opties.

Ik heb nu hetvolgende bedacht (in pseude code):
1) SELECT * FROM Regions WHERE Name LIKE 'Am%'
2) Foreach record in resultaat
pak path uit het record en sla deze op in een array
3) Creeër een query alá SELECT * FROM Regions WHERE Path Like ' + path[0] + ' OR Path LIKE ' + path[1] + ' etc.
4) Voer die query uit

Dat is helaas dus niet helemaal in SQL te doen, maar het voordeel is wel, dat er maar twee queries nodig zijn, dus zal het redelijk performen.