Date: Thu, 27 Apr 2006 00:46:31 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Ray Madigan <ray@madigans.org>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: LinkedList
Message-ID: <20060427054631.GB97354@pervasive.com>
References: <1146067158.23538.264.camel@state.g2switchworks.com>
	<IEELLOAPJFACBDBGELGHAELAEDAA.ray@madigans.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <IEELLOAPJFACBDBGELGHAELAEDAA.ray@madigans.org>
User-Agent: Mutt/1.5.11

decibel=# select * from t;
 a | b 
---+---
 1 | 0
 3 | 1
 5 | 3
 7 | 5
 2 | 0
 4 | 2
 6 | 4
 8 | 6
(8 rows)

decibel=# select * from t x join t y on(x.a=y.b) where y.a=7;
 a | b | a | b 
---+---+---+---
 5 | 3 | 7 | 5
(1 row)

decibel=# select * from t x join t y on(x.a=y.b) where y.a=8;
 a | b | a | b 
---+---+---+---
 6 | 4 | 8 | 6
(1 row)

decibel=#

As you can see, it selects the right data, but you'll need to step
through it somehow. You might be able to do it with a generate_series(),
or you can use a function. If we get WITH support/recursion in 8.2 you'd
use that.

I think that "SQL For Smarties" by Joe Celko might have an example of
how to do this without using a function. Even if it doesn't it's a book
any serious database developer should own.

On Wed, Apr 26, 2006 at 10:35:15AM -0700, Ray Madigan wrote:
> Scott,
> 
> Thanks for your reply,  I tried what you said, worked around a few things
> but I am still stuck.  The main reason is I didn't do an adequate job of
> explaining the situation.  The table implements many linked lists and I want
> to traverse one of them given the end of the list.
> 
> Say the table contains
> 
> h | v | j
> 1   0   100
> 3   1   300
> 5   3   500
> 7   5   700
> 
> 2   0   200
> 4   2   400
> 6   4   600
> 8   6   800
> 
> If I specify t.h = 8 I want to traverse the even part of the table
> If I specify t.h = 7 I want to traverse the odd part of the table
> 
> If you can send me to a book to read I am willing
> 
> Thanks
> 
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Scott Marlowe
> Sent: Wednesday, April 26, 2006 8:59 AM
> To: Ray Madigan
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] LinkedList
> 
> 
> On Wed, 2006-04-26 at 11:09, Ray Madigan wrote:
> > I have a table that I created that implements a linked list.  I am not an
> > expert SQL developer and was wondering if there are known ways to traverse
> > the linked lists.  Any information that can point me in the direction to
> > figure this out would be appreciated.  The table contains many linked
> lists
> > based upon the head of the list and I need to extract all of the nodes
> that
> > make up a list.  The lists are simple with a item and a link to the
> history
> > item so it goes kind of like:
> >
> > 1, 0
> > 3, 1
> > 7, 3
> > 9, 7
> > ...
> >
> > Any suggestions would be helpful, or I will have to implement the table
> > differently.
> 
> You should be able to do this with a fairly simple self-join...
> 
> select a.id, b.aid, a.field1, b.field1
> from mytable a
> join mytable b
> on (a.id=b.aid)
> 
> Or something like that.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461