Thanks.
I have revised the query as follows.
SELECT DISTINCT *
FROM Tbl T1 LEFT OUTER JOIN Tbl T2 ON (T1.ID = T2.Link)
WHERE T1.Name LIKE 'ZAR%';

And it now shows the main record and the child record however it shows the
main record for every child record again even with distinct.

>--- 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
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/