Thank you for responding Jakob.
Again this is the query I would like:
SELECT r.msid, r.rtitle, r.brief
FROM rstep1 r, serviceinterest si
WHERE ((si.targetmsid = 101)
AND (si.interestmsid = r.msid))
Below was my first of many attempts to solve this problem, but it wouldn't work right
using RC4 because it tries to form the INNER JOIN from the memberServiceId to the
first of two part primary key in the second table. I had not tried this particular
method in RC5, but tried other methods using RC5.
I know below is wrong, but I don't know the right strategy for what I need. I can't
seem to figure out how to handle subqueries not off the primary key.
I want a collection of these back:
<class-descriptor class="com.model.services.interest.RServiceInterestVO"
table="rstep1">
<field-descriptor
name="memberServiceId"
column="msid"
jdbc-type="BIGINT"
primarykey="true"
/>
<field-descriptor
name="rTitle"
column="rtitle"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="brief"
column="brief"
jdbc-type="VARCHAR"
/>
<!-- This would be wrong, as I don't want to go off of ServicenterestVO's
primary key, but use this object in a subquery. -->
<reference-descriptor
name="serviceInterest"
class-ref="com.model.memberservice.ServiceInterestVO"
auto-retrieve="true"
auto-update="false"
auto-delete="false">
<foreignkey field-ref="memberServiceId"/>
</reference-descriptor>
</class-descriptor>
This is what the subquery table looks like:
<class-descriptor class="com.model.memberservice.ServiceInterestVO"
table="serviceinterest">
<field-descriptor
name="targetMemberServiceId"
column="targetmsid"
jdbc-type="BIGINT"
primarykey="true"
/>
<field-descriptor
name="targetMemberId"
column="targetmid"
jdbc-type="BIGINT"
indexed="true"
/>
<field-descriptor
name="interestMemberServiceId"
column="interestmsid"
jdbc-type="BIGINT"
primarykey="true"
/>
<field-descriptor
name="interestMemberId"
column="interestmid"
jdbc-type="BIGINT"
indexed="true"
/>
</class-descriptor>
This failed as well as other things:
crit.addEqualTo("serviceInterest.targetMemberServiceId", jMsid);
crit.addEqualToField("serviceInterest.interestMemberServiceId",
"memberServiceId");
Collection result =
GenericDAO.getInstance().findCollectionByCriteria(objectClass, crit);
----- Original Message -----
From: Jakob Braeuchi <[EMAIL PROTECTED]>
Date: Wednesday, March 10, 2004 1:25 pm
Subject: Re: Still no luck with this query
> hi john,
>
> you should at least provide some information about the repository
> you use.
>
> jakob
>
> [EMAIL PROTECTED] wrote:
>
> > Dear all,
> >
> > Maybe I wasn't asking my question well because it had not gained
> a response in about a week. Maybe I am missing something very
> simple, but I am still pretty new to any kinda OR-mappings and I
> have nobody who can help but this list. Rather then try to
> explain the 4 different ways I tried to solve it again, how might
> people with better understanding. I can not find an answer to
> this in the mailing lists nor in the docs. If you have a
> reference, please give me a pointer.
> >
> > I am trying to get a listing of all of the services ('aservice')
> that are interested in my particular service (having id 101).
> >
> > // The query I would like agressively abreviated:
> > SELECT r.msid, r.rtitle, r.brief
> > FROM aservice r, serviceinterest si
> > WHERE ((si.targetmsid = 101)
> > AND (si.interestmsid = r.msid))
> >
> >
> > // I want a collection of these returned
> > CREATE TABLE aservice (
> > msid BIGINT(20) NOT NULL,
> > rtitle VARCHAR(70) NOT NULL,
> > brief VARCHAR(255) NULL,
> > PRIMARY KEY(msid),
> > )
> >
> >
> > // Note a duo key that should not be referenced in whole by
> aservice:> CREATE TABLE serviceinterest (
> > targetmsid BIGINT(20) UNSIGNED NOT NULL,
> > interestmsid BIGINT(20) UNSIGNED NOT NULL,
> > PRIMARY KEY(targetmsid, interestmsid)
> > )
> >
> >
> > I have been having problems because of foreign keys and how
> INNER JOIN seems to work with this query.
> >
> >
> > Thank you if you can. I will gladly post more detailed
> information or email the code as need be.
> >
> > JohnE
> > [EMAIL PROTECTED]
> >
> >
> >
> >
> >
> > -----------------------------------------------------------------
> ----
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
> -------------------------------------------------------------------
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
