SQL: master-detail query

[Henning von Bargen <H....@Triestram-Partner.de>]

I still haven't any response for my question concerning SQL
except one from Markus Braasch [mb@webartists.net]
who proposed to use two different files.
The master query is inside an XML file and the details are fetched inside an
XSL file
using <xsl:template match>.

This IS a possible solution, but it seems complicated to me.
And by the way, it suppose it requires another DB connection (?)

Are there any handy solutions for this?
Can the SQL processor be extended to allow a query inside a query?

If using two different files is the only solution, then this should be added
to the documentation
and an example could be helpful to many users.

Henning

Re: SQL: master-detail query

[Reinout van Rees <rr...@cti036.citg.tudelft.nl>]

On Wed, 22 Mar 2000, Henning von Bargen wrote:

> The master query is inside an XML file and the details are fetched inside an
> XSL file
> using <xsl:template match>.

> Are there any handy solutions for this?
> Can the SQL processor be extended to allow a query inside a query?

This isn't so much a cocoon issue as a SQL issue. What you effectively
seem to want is a nested "select" statement. Though this is available
in ansi-SQL, most databases don't implement it. Oracle and the likes
probably do, but not the cheaper ones.

In my opinion, cocoon is right in not supporting this. If your
database can't handle it, cocoon should handle it even less. It's not
a database.

I missed the original answer, but first using the sql processor, then
xslt-transforming it into another query and then again using the sql
processor seems the only way to achieve this with cocoon.

greetings,

Reinout

--
Reinout van Rees => R.vanRees@ct.tudelft.nl  +31-15-278-5456
"There's good and evil in all of us.
 It's up to you alone which to follow" - Geoff Mann

Re: SQL: master-detail query

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 22 Mar 2000, Henning von Bargen wrote:

> I still haven't any response for my question concerning SQL
> except one from Markus Braasch [mb@webartists.net]
> who proposed to use two different files.
> The master query is inside an XML file and the details are fetched inside an
> XSL file
> using <xsl:template match>.
> 
> This IS a possible solution, but it seems complicated to me.
> And by the way, it suppose it requires another DB connection (?)

Horribly complicated, you're right.

> Are there any handy solutions for this?
> Can the SQL processor be extended to allow a query inside a query?

probably with some ugly custom code, but there's nothing clean.

> If using two different files is the only solution, then this should be
> added to the documentation and an example could be helpful to many
> users.

i'd love it if you'd like to contribute some ;)

the only good way i've come up with to tackle this (and it's a fairly
common thing to do with SQL resultsets) is by using a special tag to
access values from the parent query -

<execute-query xmlns="http://xml.apache.org/2000/SQL">
 <query>
  select id,name from test_table
 </query>
 <execute-query>
  <query>
   select id,name from extra_table where test_id = <parent>id</parent>
  </query>
 </execute-query>
</execute-query>

but actually writing in support for this is... icky. I'm looking into
doing it in the SQLFilter for cocoon2 since this sort of thing seems
easier in SAX. Suggestions for a better syntax for accessing ancestral
resultset values are welcome - parent is obviously bad since it doesn't
allow you to go up more than one layer.

- donald