List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 7 1999 5:47pm
Subject:Re: Recursive Subqueries
View as plain text  
On Tue, 1999-09-07 05:13:05 -0700, Andy Brown wrote:
> I'm trying to create a category tree using one table. This table
> will have an auto-increment ID field, and a parent_ID field which
> will refer to an ID field.
> 
> Through a SQL statement that will create different aliases for the
> same table, I should be able to create a hierarchial view of the
> relationships.  This works on Oracle.

Standard SQL doesn't have recursive queries (one of the quite hard
limitations) and MySQL hasn't, either.

The only work-around I see for you is to use temporary tables to
iteratively construct the final result set.

As I got curious about this problem myself, I just did a small
example:

  USE test;
  CREATE TABLE tree (
    id     int(11) DEFAULT '0' NOT NULL auto_increment,
    name   char(30),
    parent int(11) DEFAULT '0',
    PRIMARY KEY (id)
  );
  INSERT INTO tree VALUES
   (1,'/',NULL)
  ,(2,'usr/',1)
  ,(3,'var/',2)
  ,(4,'datei.txt',3)
  ,(5,'tmp/',1)
  ,(6,'home/',1)
  ,(7,'ramsch/',6)
  ,(8,'.bashrc',7)
  ,(10,'.forward',7)
  ,(11,'Mail/',7);

To construct the full path names, I need a temporary table for the
intermediate results:

  CREATE TABLE tmp1 (
    ... same definition as table tree ...
  );

Now for a start, copy all entries over:
  INSERT INTO tmp1
    SELECT * FROM tree;

The step of iteration now is to join with tree to prepend the next
path component, where necessary.  But because MySQL can't INSERT INTO
the same table it selects from, we need a 2nd temporary table:

  CREATE TABLE tmp2 (
    ... same definition as table tree ...
  );

Now the iteration step itself:

a) REPLACE INTO tmp2
     SELECT t2.id
          , CONCAT(t1.name,t2.name) AS name
          , t1.parent
     FROM tree AS t1
        , tmp1 AS t2
     WHERE t1.id=t2.parent;
   
b) /* And copy back into tmp1: */
   REPLACE INTO tmp1
     SELECT * FROM tmp2;

If the column parent still has non-NULL entries, we repeat this step.
(BTW, an easier test is, if a) has 0 affected columns.)


The final result then is
  SELECT * FROM tmp1;


I hope, this exmaple may help somebody - it's been instructive to me
at least ... :)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Recursive SubqueriesAndy Brown7 Sep
  • Re: Recursive SubqueriesMartin Ramsch7 Sep
RE: Recursive SubqueriesJohn Millaway7 Sep