off the top of my head, start with this (easiest) to get it working. If it
performs well, you can leave it like this, but for better performance take
a look at http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Groups
which will let you use a single join query to accomplish your goal.
...
<esql:query>select id, area from ukareas order by area</esql:query>
<esql:results>
<esql:row-results>
<area>
<name><esql:get-string column="area"/></name>
<esql:execute-query>
<esql:query>
select county from county, navmap
where navmap.cid = county.id
and aid= <esql:parameter
type="int"><esql:get-int column="id"/></esql:parameter>
order by county
</esql:query>
<esql:results>
<counties>
<esql:row-results>
<county><esql:get-string
column="county"/></county>
</esql:row-results>
</counties>
</esql:results>
</esql:execute-query>
<area>
</esql:row-results>
</esql:results>
-Christopher
|---------+---------------------------->
| | Jason Lane |
| | <[EMAIL PROTECTED]|
| | > |
| | |
| | 06/01/2004 09:51 |
| | AM |
| | Please respond to|
| | users |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Subject: dynamic query help
|
>--------------------------------------------------------------------------------------------------------------|
Hi list,
I have this ESQL query to my DB:
<esql:query>select id, area from ukareas order by area</esql:query>
This just returns the column rows "ID" and "AREA"
I also have this table which maps tables ukareas and county
create table navmap (
aid integer,
cid integer,
primary key (aid,cid),
foreign key (id) references ukareas(id),
foreign key (id) references county(id)
);
I then have this query:
<esql:query>select county from county, navmap where navmap.cid =
county.id and aid= 'X ' order by county</esql:query>
Where 'X' is the result of ukareas.id column, so essentially I'd like
to do something like
for (int i = 0; i < id.length; i++) {
//where id = ukareas.id
<esql:query>select county from county, navmap where navmap.cid
=
county.id and aid= '<xsp:expr>id[i]</xsp:expr>' order by
county</esql:query>
}
essentially I'd be outputing something like:
South - East
Surrey, Kent, etc...
Anglia
Norfolk, Suffolk, etc...
I have a very, very rough idea of how I could do this but need some
pointers.
Many thanks all.
Jason
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
