--- In firebird-support@yahoogroups.com, Uwe Oeder <uweo@c...> wrote:

> I would like to query a recursive table but am not getting the child
> records. The table looks like the following : Tbl (ID : Integer
> , Link : Integer , Name : Char) My SQL Query looks as follows but
> only returns the parent record. The Link field references the ID
> field and determines who is the parent record.
>
> SELECT T1.Name
> FROM Tbl T1 LEFT OUTER JOIN Tbl T2 ON (T1.ID = T2.Link)
> WHERE T1.Name LIKE 'ZAR%';
>
> I would like to query multiple levels , can somebody show me what I
> am doing wrong. Also is it possible given a condition like this to
> find all Parents records upwards in the hierachy ?

I'm sorry, but I don't think there is anything as a recursive query.
Basically, the query above is equal to

SELECT T1.Name
FROM Tbl
WHERE T1.Name LIKE 'ZAR%'

since you do not select anything from T2. Outer joins just make it
possible to add information to your output set if there is any such
output, e.g.

SELECT T1.Name, T2.Name
FROM Tbl T1 LEFT OUTER JOIN Tbl T2 ON (T1.ID = T2.Link)
WHERE T1.Name LIKE 'ZAR%';

would possibly have given you two levels (though in two different
columns).

To do what you want, I think you have to write a stored procedure
calling itself. Just make sure you jump out of it in an orderly
fashion and that you never make it go too deep (I've never written a
recursive stored procedure, but I think I have read that others have
done so).

Set