asked on

Select from a self-referenced MySql Table

I have a MySql question that is out of my league. I have a self reference hierarchical table. Every row is a node in the tree. The parent column points at the id of its parent, the master.

TABLE MyTable1
id int(11) NOT NULL AUTO_INCREMENT,
parent int(20) DEFAULT NULL,
aData varchar(50) DEFAULT NULL

I also have a secondary table:

TABLE MyTable2
id int(11) NOT NULL AUTO_INCREMENT,
id_table1 int(20) DEFAULT NULL,
aData varchar(50) DEFAULT NULL

The id_table1 points at the id column in table1 it belongs to.

Now i ned to make two select that from any node (identified by id column in MyTable1)

(1) get all of its branches (or children).
(2) make another select that gives me all the rows of the MyTable2 that is owned by any of the just selected rows in MyTable1.

Regards, Paer.

noci

So that is a join with the same table...
select t1.id, t2.id, t2.adata.... from table1 as t1 outer join table1 as t2 ON t1.id = t2.parent WHERE .....

Paer Toernell

ASKER

Yes, the first part is only one table

noci

As i tried to tell in the first answer: you need to look into JOIN statement and also into table alias names
like the table1 as t1 and table1 as t2 so you can handle one table in both parts of a join/select etc.

Paer Toernell

ASKER

Yes, I do assume i have to use join. I use join a lot. But in this case I cant figure out how.

ASKER CERTIFIED SOLUTION

noci

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paer Toernell

ASKER

Ok, but how do i make it recursive?

noci

recursive?.....

say you have:
id=1, parent=0,data=100
id=2, parent=1,data=200
id=3, parent=1,data=300
id=4, parent=2,data=400
id=5, parent=4,data=500
id=6, parent=2,data=600
id=7, parent=4,data=700

that wil generate:
2,1,100
3,1,100
4,2,200
5,2,400
6,2,200
7,4,400

So now you select the one you like from here...
If you need more then select another one etc. etc.
Obviously you can extend the join to do 3, 4, 5 levels ... it just needs more complex selects with join.

There is no way to generate a query that will automagically determine levels and create a tree... (that needs to be done in code)...

Paer Toernell

ASKER

I need one select that works with every kind of tree.

Paer Toernell

ASKER

I don't know if its 3 nodes in the tree or 80 000

Paer Toernell

ASKER

In Delphi i do it with a recursive function. But im not that good with MYSQL.

Function tNodeEngine.CopyNodes(SourceNodes:tMyIntArray;  DestNode: Int64):Int64;
var
  i:Int64;
  AppendDids : tMyIntArray;
  ChildDids :  tMyIntArray;
  MyDid, MyParentDid : Int64;

begin
     cArtIntFrom.Filtered:=False;
     // Steg ett kopiera alla angivna noder
     AppendDids:=Nil;
     If Not cArtIntTo.FindKey([DestNode]) then Begin
       raise Exception.Create('cNodeTo.FindKey([DestNode])=False');
     End;
     for i := 0 to Length(SourceNodes)-1 do  begin
        If Not cArtIntFrom.FindKey([SourceNodes[i]]) Then Begin
          raise Exception.Create('cNodeTo.FindKey([DestNode])=False');
        End;
        MyDid:=AppendOneNode(DestNode);
        Result:=MyDid; // Sista nya DID returneras.
        AppendDids:=AppendDids+[MyDid];
        AppendAllPropRecs(SourceNodes[i],MyDid );
     end;

     // Steg två, sök fram barn och skicka vidare
     For i := 0 to Length(SourceNodes)-1 do  begin
      cArtIntFrom.Filter:='did_parent ='+IntToStr(SourceNodes[i]);
      cArtIntFrom.Filtered:=True;
      ChildDids:=Nil;
      cArtIntFrom.First;
      while cArtIntFrom.Eof=False do begin
          ChildDids:=ChildDids+[cArtIntFrom.FieldByName('did').AsLargeInt];
          cArtIntFrom.Next;
      end;
      if Length(ChildDids)>0 then Begin
          CopyNodes(ChildDids,  AppendDids[i] );
      End;


     end;
end;

Open in new window

SOLUTION

noci

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial