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:[EMAIL PROTECTED] Sent: Tuesday, April 15, 2008 11:36 AM To: Scott Webster Wood Cc: dbi-users@perl.org 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
