On 1 Jun 2004, at 15:27, Christopher Painter-Wakefield wrote:
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.
hmmm. I get a NullPointer on that, not a problem though I shall look into using esql-groups, I had been looking at that, it makes more sense to me now. I shall need a 3 table join as well later on so using groups may be the best most efficient course.
Thanks again Christopher.
... <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]
Jason Lane
Developer Root10 developments
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
