Recursion SQL command

[poutine]

Hi,
I have a problem now with my tables, I hqve this table named structures which I used recursion in it. It has IDStructure, Name, Tel, Mail, ParentStructure as fields. ParentStructure has the IDStructure of the Parent Structure. Is it possible to have a command that wil get me the fields of a Structure but instead of getting the ID of the ParentStructure, I want the name of it.

[poutine]

I forgot to mention, if its a the top of the structure, ParentStructure = NULL...
I Got this as code
SELECT S.IDStructures, S.Nom, S.Tel, S.Tel2, S.Fax, S.Mail, S.Mail2, S.Pager, S.Page_web, S.Adresse, S.Code_insee, P.Nom FROM Structures S, Structures P WHERE S.Structure_pere = P.IDStructures OR S.Structure_pere = NULL;
It works but just doesn't show me the Top Structures which have NULL as ParentStructure

[Old Pedant]

Here you go:

SELECT S.IDStructures, S.Nom, S.Tel, S.Tel2, S.Fax, S.Mail, S.Mail2,
S.Pager, S.Page_web, S.Adresse, S.Code_insee, P.Nom
FROM Structures S LEFT JOIN Structures P
ON S.Structure_pere = P.IDStructures

Incidentally, you can never compare a field to NULL using = or <> operators. The result of "field = NULL" is itself NULL, *NOT* either true or false (even if the field is indeed null). You must use "IS NULL" instead. But changing your query to just use IS NULL would *not* have the results you are after. Try it, to see what I mean.

So... what you really wanted here was a simple LEFT JOIN, as I show you.

BUT... But you do realize this only gets you ONE LEVEL of recursion, yes?? What do you do if you need two levels, or three levels, or 10 levels?