RE: Recursive SQL query?
- From: Erick.Nelson@xxxxxxxxxxxx (Erick Nelson)
- Date: Tue, 15 Apr 2008 11:39:37 -0700
The closest thing that comes to this would be Oracle's "Connect By
Prior" syntax. I don't know if other database vendors support this.
-----Original Message-----
From: Colin Wetherbee [mailto:cww@xxxxxxxxxxxxxxxx]
Sent: Tuesday, April 15, 2008 11:36 AM
To: Scott Webster Wood
Cc: dbi-users@xxxxxxxx
Subject: Re: Recursive SQL query?
Scott Webster Wood wrote:
let'sjust say I left Property1 blank on the link forWindows and also
in it's parent Desktop but had it filled in on theDesktop parent
Computers (as shown above). If I wanted to find the Property1 for
the sub category but found it blank, I'd like to grab thevalue from
the next parent(s) above it that did have a value.
Is there any way to writethat kind of recursive search right in the
SQL itself rather than doingtons of lookups in an 'until' type
structure in my scripting languageuntil I find a value that's not
null?
This sounds rather like a poor database design.
Each row of a table should contain one "fact", which can then be merged
via JOIN to incorporate supplementary information pertaining to that
single fact. You shouldn't really need to go recursing through a data
tree to come up with all the parts of a single fact.
For your example, which I've omitted in preference of brevity, I would
suggest adding a property table with two columns: primary key for
property id and property description. Then, instead of storing property
description text separately in your categories and categoryLinks tables,
have a foreign key reference the id column in the property table.
In addition, I'd get rid of your categoryLinks table altogether. In the
categories table, for each row that needs a parent, include a foreign
key that references that same table's primary key. Then, OUTER JOIN the
table to itself later.
If your tree is going to be very deep, keep in mind that some databases
impose (reasonable) limits on the number of JOINs that can be in a
single SQL statement. In the end, you may want to do some processing in
a stored procedure, too, rather than relying on a single SQL query to
provide your entire hierarchy.
Just my $0.02.
Also, since the DBI list isn't really the right place to ask database
design questions, you'll probably want to find a more SQL-centric forum
for future questions. PostgreSQL maintains a pgsql-sql list for
SQL-specific questions, for example.
PostgreSQL would probably be a very good choice for your implementation,
by the way, since it has extensive text searching abilities.
Colin
.
- References:
- Re: Recursive SQL query?
- From: Colin Wetherbee
- Re: Recursive SQL query?
- Prev by Date: Re: Recursive SQL query?
- Next by Date: Re: Multiple selectrow_array calls with statement handle error
- Previous by thread: Re: Recursive SQL query?
- Next by thread: RE: Recursive SQL query?
- Index(es):
Relevant Pages
|
