Re: Q: How do you do this in SQL ?

You can't. At least not to unlimited depth in a single query.

You could do it to a finite depth (n) by using an order n self-join. Alternatively, write s recursive stored procedure that builds a result set in a temporary table and then selects that table.

Satish Katiyar <katiyar_at_informix.com> wrote:

>I should know SQL better but for now can you please help me ?
>
>I have a table
>
>create table keywordclass (
> Id integer,
> KC char(1), // keyword or class ?
> Name char(15),
> Parent integer // refers to Id
>);
>
>It stores keyword hierarchies. Sample data is
>
>1, 'C', 'Living', 1
>2, 'C', 'Animal', 1
>3, 'C', 'Plant', 1
>4, 'K', 'Tiger', 2
>5, 'C', 'Bird', 2
>6, 'K', 'Parrot', 5
>
>How do you write a query in SQL that will retrieve the full
>keyword hierarchy (tree) ? (Assuming that names are unique).
>For example, retrieve the tree for "Animal", should return
>
>2, 'C', 'Animal', 1
>4, 'K', 'Tiger', 2
>5, 'C', 'Bird', 2
>6, 'K', 'Parrot', 5
>
>What will be the query if keywords and class names could be
>the same and duplicate ?
>
>Thanks a lot for your time. I would appreciate if you could
>send your reply as email.
>
>Satish
>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>Satish Katiyar Email : katiyar_at_informix.com
>Informix Software, Inc. Work : (510) 873 - 8494
>1111 Broadway, Suite 2000 Fax : (510) 873 - 6275
>Oakland, California 94607 Home : (510) 769 - 6953
>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

     Accession Systems Ltd/Sparkle Computer Co Ltd

     Client/Server development and consultancy:
          Unix, Sybase, C, Powerbuilder, Delphi 
----------------------------------------------------