SQL Challenge...

[jbkosman]

Hi, here's the problem.

The table structure would be something similar to:

[tt]tierX
-------
IDtierX (primary key, auto_increment)
IDoverX (unique)
name (not null)[/tt]

Say the data in the table is:

[tt] IDtierX | IDoverX | name
-----------+-----------+---------
1 | 2 | one
2 | 3 | two
3 | 4 | three
4 | 5 | four
5 | | five
6 | 7 | six
7 | 8 | seven
8 | | eight[/tt]

I have IDtierX and want to get all the names in a sequence where a sequence starts with IDtierX and ends where IDoverX=''.

So with the table above, if I know IDtierX = 1, I want a query that'll give me the names 'one, two, three, four, five'. If I know IDtierX = 6, I want the same query to give me the names 'six, seven, eight'.

The sequence length is variable.

Of course this could be done through multiple queries and say php, but that wouldn't be too efficient over the long run.

I'm using postgresql.

So, any suggestions? Alternate table setups are welcome as well.

Thanks, JB

 

[angiole]

So while your table structure supports parent/child, a function would be required to determine the grandparent (or grandchild depending on direction).

Oracle uses a function called 'CONNECT BY PRIOR'. Perhaps PostgresSQL has similar.

If not, you might try this.

Add a column to specify the highest level grantparent ID. This Id would be carried over to successive generations (inserts), since the insert is not doubt lookin for the parent anyway, something like this:

GP_ID ID IDOver Name

1 1 2 one
1 2 3 two
1 3 4 three
1 4 5 four
1 5 five
6 6 7 six
6 7 8 seven
6 8 eight

So then finding all the Names of grandparent 1 becomes a simple SQL.

You might also have to create a record which has a GP_ID =1, ID =1, and IDOver = 1 to initiate the grandfather sequence.

Cheers
AA 8~)

 

[jbkosman]

Hmmm, yeah.

I looked around for what you mentioned, doesn't seem Postgre supports the Oracle equivalent as I went through all the functions. However it does allow you to create custom functions using PL/pgSQL as Oracle (I believe) does.

So either that or your suggestion would work as well.

Thanks for the help, Regards. JB

 

[r937]

> Alternate table setups are welcome as well

try joe celko's nested sets

do a google search such as --

http://www.google.com/search?&q=joe+celko+nested+sets

there's some code available for download here --

http://www.mvps.org/access/queries/qry0023.htm

disclaimer: i don't use this technique myself

any time i have a hierarchy, i constrain it to no more than X levels (using application logic, or by just not nesting stuff any deeper), and then use an X-way left join to get all the subtrees -- this is straightforward and would let you keep your original table design, no changes or additional columns


rudy

http://rudy.ca/

 

[Guest_imported]

This is one of those cases where PL/SQL beats plain SQL. In other words, a LOOP is the easiest way to resolve your challenge.