SQL-fråga om trädstruktur

Jag har trädstruktur med produktgrupper som sinsemellan har relationer.
Exempel: En produktgrupp har 2 produktgrupper relaterade till sig somi sin tur var och en har flera andra relationer till andra produktgrupper.
Tabell för relationerna innehåller bara två fält med produktgruppsId och produktgruppsId för relationsproduktgruppen.

EXEMPEL:
prodId - relProdId
1 - 2
1 - 3
2 - 10
2 - 11
2 - 13
3 - 20
3 - 21
3 - 22
10 - 31
10 - 32

Om jag vill titta på alla produktgrupper som har relation till 1 så ska jag få upp 2,3 och 10,11,13,20,21,22 (relation med 2,3) och 31,32 (relation med 10 som har relation till 2).

Jag undrar lite över hur man bygger SQL-syntax för detta, jag vet inte hur många "nivåer" det kan tänkas finnas.

Hoppas det är tydligt nog :)

select relProdId from tabell where prodid=1

så får du alla under grupp 1. Men du kanske hade tänkt få alla grupper i en fråga? Det blir nog svårt. Det kanske inte ens är önskvärt. Jag håller själv på med ett program med en trädstruktur. Det jag gör är att jag använder en visuell trädvy för användaren att hålla på med. Det ger ett gränssnitt som påminner lite om Utforskaren i windows. I trädet bygger jag upp en gren i taget vartefter jag navigerar mig fram. Då behöver jag inte fråga efter alla grupper på en gång. För övrigt frågade jag efter allt ifrån början, men det visade sig ta för lång tid att läsa ut hela trädet från databasen. Så låt bli det. Fråga om lite grann i taget. En grupp åt gången.

En liten synpunkt på tabellen:
Det hade varit mer naturligt att kalla primärnyckeln för "prodId", och relationsnyckeln för "relProdId". Nu har du gjort tvärtom.

Fråga om lite grann i taget.

Går tyvärr inte, jag vill kunna visa trädet eller ta fram allt som ligger under en gren.

Det hade varit mer naturligt att kalla primärnyckeln för "prodId", och relationsnyckeln för "relProdId".

Är det inte det jag gjort?

lolukokasos skrev:

Fråga om lite grann i taget.

Går tyvärr inte, jag vill kunna visa trädet eller ta fram allt som ligger under en gren.

Ok, hur man gör det med en enda fråga vet jag inte. Det program som jag skrev om, ställde flera frågor för att få hela trädet. Du gör naturligtvis som du vill, men räkna med att det kan ta tid när trädet blir riktigt stort.

lolukokasos skrev:

Det hade varit mer naturligt att kalla primärnyckeln för "prodId", och relationsnyckeln för "relProdId".

Är det inte det jag gjort?

Nej. Primärnyckeln används för att identifiera rader i en tabell. Det går inte med "prodId" eftersom flera rader har samma "prodId". Det är bara "relProdId" som är unikt för varje rad. Alltså är det "relProdId" som du har använt som primärnyckel.

Primärnyckeln används för att identifiera rader i en tabell.

Primärnyckeln är i så fall både prodId och relProdId då dessa i sig själva kan finnas på många rader dock inte i kombination.

Problemet med att fråga om "lite grann i taget" är att jag i förväg inte med säkerhet vet hur många gånger jag behöver fråga.
Det där med svarstid kan man nog svälja... :)

lolukokasos skrev:

Problemet med att fråga om "lite grann i taget" är att jag i förväg inte med säkerhet vet hur många gånger jag behöver fråga.

Varför skulle det vara ett problem? Du ställer en fråga om hur många underprodukter som finns till en viss produkt. Sedan, för varje underprodukt, frågar du hur många underprodukter den har. Du behöver inte ha en blekaste aning i förväg om hur ofta du behöver fråga.

Självklart kan man lösa detta med "många loopar" i logiken men en gren kan ha 10 grenar under sig medan någon annan gren kanske bara har 2.
Jag skulle vilja hitta någon typ av rekursiv lösning som fungerar utan tillkrånglad logik. Det är möjligt att min nuvarande databasstruktur är förkastlig. Jag är öppen för förslag! :)

http://www.intelligententerprise.com/001020/celko.shtml

kanske

rekursion sa du. Vilken dbms ?

Självklart kan man lösa detta med "många loopar" i logiken men en gren kan ha 10 grenar under sig medan någon annan gren kanske bara har 2.

Tänk dig något i stil med (bara pseudokod för att illustrera idén):

Sub hämtaUnderNivå( prodId as long)

'frågar efter alla poster i en gren
rsTabell.Open sqlsträng

'Går igenom alla poster i den grenen
while rsTabell.EOF = false

        'rekursivt anrop med id för post inne i grenen för att kunna fråga efter poster i dess undergren
       hämtaUnderNivå( rsTabell!prodId)

       'gå fram ett steg till i recordsetet
       rsTabell.MoveNext
End While

end sub

Något i den här stilen skulle du kunna göra i vb. I andra programmeringsspråk kan det finnas andra metoder för att stega dig fram i varje gren. Men principen bör vara den samma.

LarsG: Intressant läsning, man tackar :D
Jag har SQL-server 2000.
När jag väl förstått detta så kommer nog någon fråga ;).

UlfT: kommer inte din kod i förra inlägget att skrika...den försöker väl öppna samma öppna recordset flera gånger...det brukar datorn inte gilla.

lolukokasos skrev:

UlfT: kommer inte din kod i förra inlägget att skrika...den försöker väl öppna samma öppna recordset flera gånger...det brukar datorn inte gilla.

Jag erkänner att jag inte har prövat koden, men så som jag ser det så ska recordsetet deklareras lokalt inne i suben (glömde förtydliga detta). Då borde recordsetet bara finnas inom sin egna instans av suben. När du alltså går in i suben igen genom ett rekursivt anrop, då är det alltså ett annat recordset, trots att namnet är detsamma. Förutsatt att det är deklarerat lokalt förstås.

Läst lite från http://www.intelligententerprise.com/001020/celko.shtml , och det är klart att den strukturen gör det enkelt att få fram "trädet".
Blir det inte lite knepigt ifall man vill flytta en gren eller göra så att en gren även sitter på en annan gren (en produktgrupp har flera relationer)?

UlfT: Du har säkert rätt om rekursiva funktionen, men jag tror att jag helst vill att dbms'n ska sköta jobbet.

lolukokasos skrev:

Läst lite från http://www.intelligententerprise.com/001020/celko.shtml , och det är klart att den strukturen gör det enkelt att få fram "trädet".
Blir det inte lite knepigt ifall man vill flytta en gren eller göra så att en gren även sitter på en annan gren (en produktgrupp har flera relationer)?

Är det "adjacency"-varianten du har valt? I så fall borde du kunna flytta en gren (i deras exempel) med:

UPDATE Personnel
SET Boss='Albert'
WHERE Boss='Chuck'

Vill du att en gren sitter på flera grenar, behöver du en till tabell för själva relationerna:

Relation
Boss (referens till emp i Personnel) Emp (Också en ref till emp i Personnel)

I den tabellen får varje rad ange en "Boss-underställd"-relation. Då kan en person ha flera chefer. I det fallet skulle du inte låta "Personnel"-tabellen ta hand om relationerna.

I det fallet skulle du inte låta "Personnel"-tabellen ta hand om relationerna.

Det var det jag syftade på. Hmm, kanske får satsa på någon typ logisk lösning utanför dbms.
Tack för diskussionen :)!