Probleme mit Select Statement

[Kedariodakon]

Meine Tabelle

SQL-Code:

/* Die Tabelle - swgResourceClass */
Create Table swgResourceClass
(
  Id         Int            Not Null Primary Key Clustered,
  ParentID   Int                     Foreign Key References swgResourceClass( Id ) Null, 
  SwgCraftId VarChar( 30  ) Not Null,
  Name       VarChar( 100 ) Not Null
)

Meine Testdaten in der Tabelle

SQL-Code:

/* BasisKlassen */
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 1, Null, 'ino', 'Inorganic' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 2, Null, 'org', 'Organic'   )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 3, Null, 'egy', 'Energy'    )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 4, Null, 'ast', 'Space'     )

/* Children Klassen von Inorganic */
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 5, 1, 'gem', 'Gemstone' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 6, 1, 'lgo', 'Low-Grade Ore' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 7, 1, 'mtl', 'Metal' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 8, 1, 'rad', 'Radioactive' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 9, 1, 'spc', 'Solid Petrochem Fuel' )

/* Children Klassen von Gemstone */
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 10, 5, 'cry',  'Crystalline Gemstone' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 11, 5, 'amo',  'Amorphous Gemstone' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 12, 5, 'lgem', 'Low Quality Gemstone' )
Insert Into swgResourceClass ( Id, ParentID, SwgCraftId, Name ) Values ( 13, 5, 'ugt',  'Unknown Gem Type' )

Alle Daten vom Select

Code:

/* Daten (Select * From swgResourceClass) */
/*
Id  PaentId  SwgCraftId  Name
1   NULL     'ino'       'Inorganic'
2   NULL     'org'       'Organic'
3   NULL     'egy'       'Energy'
4   NULL     'ast'       'Space'
5   1        'gem'       'Gemstone'
6   1        'lgo'       'Low-Grade Ore'
7   1        'mtl'       'Metal'
8   1        'rad'       'Radioactive'
9   1        'spc'       'Solid Petrochem Fuel'
10  5        'cry'       'Crystalline Gemstone'
11  5        'amo'       'Amorphous Gemstone'
12  5        'lgem'      'Low Quality Gemstone'
13  5        'ugt'       'Unknown Gem Type'
*/

Info: Die Klassen können unterschiedlich tief sein...

Nun ich habe folgendes Problemchen, ich möchte alle Klassen und Childrens einer Klasse ausgeben...
Das heißt ich versuche im oben genanntem Beispiel alle Klassen auszugeben, die zur Klasse 'Inorganic' gehören...

Das heißt:
- Der Datensatz mit der (Id = 1)
- Die Datensätze mit der (ParentId = 1)
Und nun zum Problem:
- Die Datensätze mit der (ParentId = ( Id von Allen mit der ParentId = 1 ))
- Die Datensätze mit der (ParentId = ( ParentId = ( Id von Allen mit der ParentId = 1 )))
... (usw) ...

Mit den obrigen Daten sollte das Result so ausschaun (Name = 'Inorganic'):

Code:

/*
Id  PaentId  SwgCraftId  Name
1   NULL     'ino'       'Inorganic'
5   1        'gem'       'Gemstone'
6   1        'lgo'       'Low-Grade Ore'
7   1        'mtl'       'Metal'
8   1        'rad'       'Radioactive'
9   1        'spc'       'Solid Petrochem Fuel'
10  5        'cry'       'Crystalline Gemstone'
11  5        'amo'       'Amorphous Gemstone'
12  5        'lgem'      'Low Quality Gemstone'
13  5        'ugt'       'Unknown Gem Type'
*/

Nun, ich hab keine Ahnung wie ich so ein verschachteltes Select mache, falls sowas überhaupt geht...
Hat da jemand vielleicht eine Idee dazu?

Bye Keda

[Kedariodakon]

Bisher sieht mein Test-Statement so aus, dies geht so nur bis zur 3.Ebene...

SQL-Code:

/* Erste Ebene */
Select *
From swgResourceClass
Where Name = 'Inorganic'
  Union
/* Zweite Ebene */
Select *
From swgResourceClass
Where ParentID In ( Select ID From swgResourceClass Where Name = 'Inorganic' )
  Union
/* Dritte Ebene */
Select *
From swgResourceClass
Where ParentID In ( Select ID From swgResourceClass Where ParentID In ( Select ID From swgResourceClass Where Name = 'Inorganic' ) )
Order By ID

Problem ist halt wie gesagt, dass es zur Zeit mit meinen Daten bis zu 6. Ebenen sind, nun aber ein Benutzer weitere erstellen könnte und jedesmal wenn ich suche ja auch alle Unions durchsucht werden, egal ob da noch Daten sind oder nicht...

Bye Christian

[Kedariodakon]

Hmm mein Ergebniss:

SQL-Code:

Declare @Result Table ( Id Int )
Declare @Ids    Table ( Id Int )
Declare @Temp   Table ( Id Int )
Insert Into @Result Select Id
  From swgResourceClass
  Where Name = 'Inorganic'
Insert Into @Ids Select Id
  From swgResourceClass
  Where Name = 'Inorganic'
While ( Select Count( Id ) From swgResourceClass Where ParentID In ( Select Id From @Ids ) ) <> 0
Begin
  Delete From @Temp
  Insert Into @Temp Select Id From @Ids
  Delete From @Ids
  Insert Into @Ids Select Id
  From swgResourceClass Where ParentID In ( Select Id From @Temp )
  Insert Into @Result Select Id
  From swgResourceClass Where ParentID In ( Select Id From @Temp )
End
Select * From swgResourceClass Where Id In ( Select Id From @Result )

Das geht soweit, aber vielleicht hat ja wer eine Idee wie es besser geht

Bye Christian

[omata]

Moin,

was ich nicht so ganz verstehe ist der Zugriff. Warum über SQL Query Analyzer?

Naja, ich habe mal eine Prozedur auf dem Server angelegt...

SQL-Code:

CREATE PROCEDURE [LoadTree] (@Aparent INTEGER = NULL, @Aebene INTEGER = 0) AS 

SET NOCOUNT ON

DECLARE @id INTEGER
DECLARE @bez VARCHAR(100)
DECLARE @zeilen CURSOR
DECLARE @ebene INTEGER

SET @ebene = @Aebene

IF @Aebene = 0 BEGIN
  CREATE TABLE ##temp (ID INTEGER, bez VARCHAR(100))
END

IF @Aparent IS NULL BEGIN
  SET @zeilen = CURSOR FOR
    SELECT id, bezeichnung
    FROM tabelle
    WHERE parent_id IS NULL
    ORDER BY parent_id, bezeichnung
END
ELSE BEGIN
  SET @zeilen = CURSOR FOR
    SELECT id, bezeichnung
    FROM tabelle
    WHERE parent_id = @Aparent
    ORDER BY parent_id, bezeichnung
END

OPEN @zeilen
FETCH NEXT FROM @zeilen INTO @id, @bez
WHILE @@FETCH_STATUS = 0 BEGIN
  INSERT ##temp VALUES (@id, @bez)
  SET @ebene = @ebene + 1
  EXEC loadtree @id, @ebene
  FETCH NEXT FROM @zeilen INTO @id, @bez
END
CLOSE @zeilen
DEALLOCATE @zeilen

IF @Aebene = 0 BEGIN
  SELECT *
  FROM ##temp
  DROP TABLE ##temp
END

SET NOCOUNT OFF

Aufruf im SQL Query Analyzer:
EXEC LoadTree oder wenn ein Unterbaum geladen werden soll...
EXEC LoadTree 1 1 steht hier für: zeige alle Elemente die die ParentID = 1 haben und deren Unterelemente.

In Delphi würde das folgendermaßen aussehen (Zugriff über DBExpress)...

Delphi-Quellcode:

procedure TForm.LoadTree(ATree: TTreeView;
                         ASQLConnection:TCRSQLConnection;
                         ANode:TTreeNode;
                         AStream:TMemoryStream;
                         AID: integer);
var SDS:TSimpleDataSet;
    Stream:TMemoryStream;
begin
  Stream:=nil;
  try
    if not assigned(AStream) then
      Stream:=TMemoryStream.Create
    else
      Stream:=AStream;

    SDS:=TSimpleDataSet.Create(Self);
    try
      SDS.Connection:=ASQLConnection;
      SDS.DataSet.CommandText:=
        'SELECT *'#13 +
        'FROM tabelle'#13 +
        'ORDER BY parent_id, bezeichnung';

      if not assigned(AStream) then
        SDS.SaveToStream(Stream, dfXMLUTF8);

      SDS.LoadFromStream(Stream);
      SDS.Filtered:=false;
      if AID >= 0 then
        SDS.Filter:='parent_id = '+inttostr(AID)
      else
        SDS.Filter:='parent_id IS NULL';
      SDS.Filtered:=true;
      SDS.First;
      while not SDS.Eof do begin
        LoadTree(
          ATree,
          ASQLConnection,
          ATree.Items.AddChild(ANode, SDS.FieldByName('bezeichnung').AsString),
          Stream,
          SDS.FieldByName('id').AsInteger
        );
        SDS.Next;
      end;
    finally
      SDS.free;
    end;
  finally
    if not assigned(AStream) and assigned(Stream) then
      Stream.free;
  end;
end;

Aufruf:
  LoadTree(TreeView, SQLConnection); MfG
Thorsten

[Kedariodakon]

Zitat von omata:

...
was ich nicht so ganz verstehe ist der Zugriff. Warum über SQL Query Analyzer?
...

Weil ich noch dabei bin ein geeignetes DB-Konzept aufzustellen und erst dann mit dem schreiben des Programms beginne, ka ob das schlau ist, aber ich versuchs diesmal so, bevor ich später dauernt von vorn begin
Dementsprechend teste ich halt die benötigten Statements im SQL Query Analyzer

Edit: Achso und danke für deine Lösung, ich werd sie mir nacher mal näher zu Gemüte führen
Edit: Nun ja und wie man sieht, bin ich kein Profi im Entwickeln von DB-Konzepten...

Bye