[SQL] Query en hierarchie

[jelmervos]

Ik 2 tabellen: tabel "Code" beschrijft een hierarchie en tabel "Item" bevat items welke verwijzen naar een item in de hierarchie. Deze voorbeeld data zit erin:

INSERT INTO Code VALUES
(1, NULL, 'Root'),
(2, 1, 'Child1'),
(3, 1, 'Child2'),
(4, 2, 'Child3'),
(5, 3, 'Child4'),
(6, 4, 'Child5')

INSERT INTO Item VALUES
(1, 2, 'Item1'),
(2, 3, 'Item2'),
(3, 4, 'Item3'),
(4, 5, 'Item4'),
(5, 6, 'Item5')

Nu wil ik op basis van Code records uit de Item tabel filteren. Bijvoorbeeld alle items welke verbonden zijn aan Child5 en hoger. Dat zijn dus Item5 (zit aan Child5) en Item3 (zit aan Child3).

Nu gaat het in de praktijk om zeer veel records. Daarom is het van belang dat de query's snel antwoord geven. Hoe is dit op te lossen? Kan dit alleen met SQL?

Uiteraard hoef ik geen kant en klare oplossing, maar ik kan me voorstellen dat dit al een keer is bedacht, en wil niet opnieuw het wiel uitvinden.

[Woy]

Hier heb je een artikel die over hiërarchische data gaat: http://www.sitepoint.com/article/hierarchical-data-database/

je zegt dat Item1 aan Child2 zit, maar ik zie niet hoe, want Item1 heeft als 2e getal 2 staan ( ik ga ervanuit dat dat de FK naar de Code tabel is ). Bij Id 2 in de code tabel zie ik Child1 staan.

[jelmervos]

Woy schreef op vrijdag 24 april 2009 @ 11:52:
Hier heb je een artikel die over hiërarchische data gaat: http://www.sitepoint.com/article/hierarchical-data-database/

je zegt dat Item1 aan Child2 zit, maar ik zie niet hoe, want Item1 heeft als 2e getal 2 staan ( ik ga ervanuit dat dat de FK naar de Code tabel is ). Bij Id 2 in de code tabel zie ik Child1 staan.

Je hebt gelijk, foutje in mijn voorbeeld. Aangepast.
Ik zal je linkje even lezen, bedankt.

[Remus]

Niet helemaal mijn forte, maar als je database recursive common table expressions ondersteunt dan is het daarmee mogelijk. Ik weet iig dat Firebird en MS SQL Server 2005 en hoger dit ondersteunen.

Je krijgt dan iets als

SQL:

WITH RECURSIVE hierarchy AS
   SELECT itemid, parentid, name
   FROM Code
   WHERE parentid IS NULL
   UNION ALL
   SELECT c.itemid, c.parentid, c.name
   FROM Code c INNER JOIN hierarchy h
   ON c.parentid = h.itemid
SELECT v.name
FROM hierarchy h INNER JOIN Value v
ON h.itemid = v.itemid
WHERE h.parentid = 4

Dit zal wel niet helemaal correct zijn, want ik heb nog nooit serieus met recursive CTEs gewerkt, maar volgens mij zou dit ongeveer wel een oplossingsrichting zijn.

[Janoz]

Kunnen er meerdere childs aan 1 item hangen? Volgens je database voorbeeldje wel, maar aan je verhaal lijkt het een 1 op 1 relatie. In dat geval vraag ik me af waarom je dit in twee tabellen opslaat.

[leuk_he]

Oracle kent weer START/Connect constructie..

http://www.adp-gmbh.ch/ora/sql/connect_by.html

In ANSI SQL kan het niet denk ik, moet je het uitprogrammeren in de taal die je gebruikt om de databse te accessen, zie .b.v. link van Woy.

[Remus]

leuk_he schreef op vrijdag 24 april 2009 @ 12:53:
Oracle kent weer START/Connect constructie..

http://www.adp-gmbh.ch/ora/sql/connect_by.html

In ANSI SQL kan het niet denk ik, moet je het uitprogrammeren in de taal die je gebruikt om de databse te accessen, zie .b.v. link van Woy.

WITH / WITH RECURSIVE is in ieder geval in SQL2003 (ISO/IEC 9075) gedefinieerd (optional features T121, T122, T131 en T132), mogelijk was deze ook al in eerdere versie van de standaard gedefinieerd.