Hello,

in our system, we want to classify messages (stored in a table) with
certain attributes (sort them into categories). Later, we want to search
for messages with certain attributes/categories. But don't only want to
be able to search for specific attributes, but also for sub-categories.

Let's say we have to following category tree:

+- Language (1)
+- Sales (5)
+- Technical Issues (9)
+- General Questions (10)

This would be stored in a table like this:

id | parent | title
---+--------+--------------
1 | null | Language
2 | 1 | English
3 | 1 | German
4 | null | Topic
5 | 4 | Sales
6 | 5 | Accounting
7 | 5 | Pricing
8 | 4 | Service
9 | 8 | Technical Issues
10 | 8 | General Questions

Now we have hundrets of support requests stored like this:

id | text
---+----------
1 | I want great pricing!
2 | My website doesn't work.
3 | Wo finde ich meine Rechnung?

These are linked to 0 or more attributes:

message_id | attribute_id
-----------+-------------
1 | 2
1 | 7
2 | 9
3 | 3
3 | 6
3 | 10

Now how could I search for messages with a topic of the category
"Service"? In the end, the sql query would have to look like this:

select m.* from messages m, attributes a where m.id = a.message_id and
a.attribute_id in (8, 9, 10);

This should return these messages:

id | text
---+----------
2 | My website doesn't work.
3 | Wo finde ich meine Rechnung?

But getting the list "8, 9, 10" ust from the top category "Service" is
somewhat tricky (recursion).

Actually, I would like to have clean code like this:

select m.* from messages m, attributes a where m.id = a.message_id and
a.attribute_id in get_category_list(8);

So get_category_list() would have to be implemented like this (pseudo code):

function get_category_list ($id) {
foreach $subcategory (select id from categories where parent = $id) {
get_category_list($subcategory);
}
return $id; # add id to result list
}

I don't know much about PL/SQL at this time. Is it possible to write
such a recursive function?

Kind regards
Marten

On Dec 11, 5:32 pm, Marten Lehmann <lehmannmap... (AT) cnm (DOT) de> wrote:
Yes it is.

Roger Gorden
Sr. DBA Skila Corp.

Fine. Do you have any examples or a good reference/link about this?

Regards
Marten

Marten Lehmann schrieb:
Problems of this sort are usually solved in Oracle by means of
hierarchical queries aka *connect by* queries. You have to consult docs
under http://tahiti.oracle.com (in particular SQL Reference) to
understand, how it works, here is small example based on your data.

SQL> with t as (
2 select 1 id,null parent,'Language' title from dual union all
3 select 2,1,'English' from dual union all
4 select 3,1,'German' from dual union all
5 select 4,null,'Topic' from dual union all
6 select 5,4,'Sales' from dual union all
7 select 6,5,'Accounting' from dual union all
8 select 7,5,'Pricing' from dual union all
9 select 8,4,'Service' from dual union all
10 select 9,8,'Technical Issues' from dual union all
11 select 10,8,'General Questions' from dual
12 )
13 -- End test data
14 select id,parent,title,lpad(' ',(level-1)*3,' ')||Title tree
15 from t
16 connect by prior id=parent
17 start with parent is null
18 /

ID PARENT TITLE TREE
---------- ---------- ----------------- -------------------------
1 Language Language
2 1 English English
3 1 German German
4 Topic Topic
5 4 Sales Sales
6 5 Accounting Accounting
7 5 Pricing Pricing
8 4 Service Service
9 8 Technical Issues Technical Issues
10 8 General Questions General Questions

Note, the part between With and -- End test data is not essential for
your query - it is only to represent your test data, on your real tables
you'll don't need this.
Now , how to get the id's (8,9,10):

SQL> with t as (
2 select 1 id,null parent,'Language' title from dual union all
3 select 2,1,'English' from dual union all
4 select 3,1,'German' from dual union all
5 select 4,null,'Topic' from dual union all
6 select 5,4,'Sales' from dual union all
7 select 6,5,'Accounting' from dual union all
8 select 7,5,'Pricing' from dual union all
9 select 8,4,'Service' from dual union all
10 select 9,8,'Technical Issues' from dual union all
11 select 10,8,'General Questions' from dual
12 )
13 -- End test data
14 select id
15 from t
16 connect by prior id=parent
17 start with title='Service'
18 /

ID
----------
8
9
10

In addition, there is usually no need to resort to PL SQL, if task
easily can be solved with SQL - mostly it is more performant solution
which can be easier maintained.

Best regards

Maxim