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@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@informix.com >Informix Software, Inc. Work : (510) 873 - 8494 >1111 Broadway, Suite 2000 Fax : (510) 873 - 6275 >Oakland, California 94607 Home : (510) 769 - 6953 >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ---------------------------------------------------- David Whitmarsh Accession Systems Ltd/Sparkle Computer Co Ltd Client/Server development and consultancy: Unix, Sybase, C, Powerbuilder, Delphi ----------------------------------------------------