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

From: Alexander Ponomarenko <shura_at_bitcom.msk.ru>
Date: 1996/08/21
Message-ID: <MAPI.Id.0016.00687572612020204433303030303143_at_MAPI.to.RFC822>#1/1

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

Hello Satish,
I think you need recursive tree search. As I know, this cannot be done with a single SQL query (Or I'm wrong?) When I had similar problem, I wrote two simple stored procedures (not a good SPL style example, but they work for me):

create procedure RECURSE_KEYWORDCLASS (KWORD_ID int) define TEMP_ID int;
  foreach
    select K.ID into TEMP_ID from KEYWORDCLASS K       where K.PARENT = KWORD_ID
    insert into TEMP_KWORD (ID) values (TEMP_ID);     call RECURSE_KEYWORDCLASS (TEMP_ID);   end foreach;
end procedure;

create procedure GET_SUBCLASS (CLASS_ID int) define RET_ID int;
  begin
    on exception

      create table TEMP_KWORD (
        ID int not null primary key);

    end exception with resume
    delete from TEMP_KWORD;
  end
  insert into TEMP_KWORD(id) values (CLASS_ID);   call RECURSE_KEYWORDCLASS (DIVISION_ID); end procedure;

You should firstly execute GET_SUBCLASS(Parent_Id), and then get all siblings from TEMP_KWORD.

If I'm dumb, and this can be done with single SQL query, or someone will provide you better solution, please let me know.

  Shura.

>

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=
> 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
>

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=

---
Shura Ponomarenko <shura_at_bitcom.msk.ru> 
System Manager, BITCOM Ltd, Moscow.

Received on Wed Aug 21 1996 - 00:00:00 CEST