Looking for a different slant on this SQL Problem

From: James Alexander Starritt <james_at_jamesstarritt.com>
Date: Thu, 25 Jan 2001 09:48:17 -0600
Message-ID: <94phtk$enu4j$1@ID-68406.news.dfncis.de>

Ok, I have come across a problem that I am determined to beat using SQL and have not managed to. My theory is right but my implementation might be sloppy, so I am looking for a more refined statement and little help on a wee hitch I am having with the problem.

I have a table which for the sake of this example will be called Terms

Genre_ID (num key), Genre, Broader_ID

This table was desinged (not by me) to hold a heirachy of terms. Each term is linked to its successor where Broader_ID = Genre_ID. This has been just fine for the way we have used it in the past but a user has asked for us to produce a heirachy report thats shows a Genre and its Sub Genre's .... The maximum depth is 12. I being reletivly new to SQL, dived into Microsoft Access 2000, and using there qyuery tools and an ODBC link created a query that included Genre and twelve aliases of Genre.

I guess I was trying to produce a recursive search of sorts and it worked perfectly if slowly. 28,000 terms.

I then started to create the query in Oracle SQL Plus. This is what I have come up with:

select a.genre, b.genre, c.genre, d.genre, e.genre, f.genre, g.genre, h.genre, i.genre, j.genre, k.genre, l.genre from
 terms a, terms b, terms c, terms d, terms e, terms f, terms g, terms h, terms i, terms j, terms k, terms l where
b.broader_id = a.genre_id

AND c.broader_id = b.genre_id
AND d.broader_id = c.genre_id
AND e.broader_id = d.genre_id
AND f.broader_id = e.genre_id
AND g.broader_id = f.genre_id
AND h.broader_id = g.genre_id
AND i.broader_id = h.genre_id
AND j.broader_id = i.genre_id
AND k.broader_id = j.genre_id
AND l.broader_id = k.genre_id

Using this query I can display the full heirachy top down. The problem comes when I want to display a branch. If I add critea ie

....

AND k.broader_id = j.genre_id
AND l.broader_id = k.genre_id
AND a.genre = 'Tools';

I get no rows returned! ... It works in Access but not in Oracle and I know its me not creating the SQL statements in either the wrong order something similar. Here is the Access statement for those with a familiarity with it ...

SELECT TERMS.GENRE, TERMS_1.GENRE, TERMS_2.GENRE, TERMS_3.GENRE, TERMS_4.GENRE, TERMS_5.GENRE, TERMS_8.GENRE, TERMS_7.GENRE, TERMS_6.GENRE, TERMS_9.GENRE
FROM ((((((((TERMS LEFT JOIN TERMS AS TERMS_1 ON TERMS.GENRE_ID =

TERMS_1.BROADER_ID) LEFT JOIN TERMS AS TERMS_2 ON TERMS_1.GENRE_ID =
TERMS_2.BROADER_ID) LEFT JOIN TERMS AS TERMS_3 ON TERMS_2.GENRE_ID =
TERMS_3.BROADER_ID) LEFT JOIN TERMS AS TERMS_4 ON TERMS_3.GENRE_ID =
TERMS_4.BROADER_ID) LEFT JOIN TERMS AS TERMS_5 ON TERMS_4.GENRE_ID =
TERMS_5.BROADER_ID) LEFT JOIN TERMS AS TERMS_8 ON TERMS_5.GENRE_ID =
TERMS_8.BROADER_ID) LEFT JOIN TERMS AS TERMS_7 ON TERMS_8.GENRE_ID =
TERMS_7.BROADER_ID) LEFT JOIN TERMS AS TERMS_6 ON TERMS_7.GENRE_ID =
TERMS_6.BROADER_ID) LEFT JOIN TERMS AS TERMS_9 ON TERMS_6.GENRE_ID =
TERMS_9.BROADER_ID

WHERE (((TERMS.GENRE)=[Enter Term]));

Any help with this problem would be appreciated, especially any advice on making this a little more efficient. I can't change the data structure so I am stuck with this for now .....

--

James Alexander Starritt (james_at_jamesstarritt.com)

Received on Thu Jan 25 2001 - 09:48:17 CST