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

.

Relevant Pages

  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)
  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Storing hierarchies from XML into relational tables
    ... The table will need a primary key so that it can be updated. ... This basic idea is the use the OUTPUT clause to get back the new ID's from the INSERT, then update the Parent field with the new ID's as appropriate. ... INSERT INTO T10 ... ID INT IDENTITY PRIMARY KEY, ...
    (microsoft.public.sqlserver.xml)