Sorry for the confusion. I tried to abbreviate to make it easier to read.
In the end, I want to do this query:
SELECT r.msid, r.rtitle, r.brief
FROM rstep1 r, serviceinterest si
WHERE ((si.targetmsid = 101)
AND (si.interestmsid = r.msid))
Based on these two tables and the repository I gave previously:
CREATE TABLE rstep1 (
msid BIGINT(20) NOT NULL,
rtitle VARCHAR(70) NOT NULL,
brief VARCHAR(255) NULL,
PRIMARY KEY(msid),
)
CREATE TABLE serviceinterest (
targetmsid BIGINT(20) UNSIGNED NOT NULL,
interestmsid BIGINT(20) UNSIGNED NOT NULL,
targetmid BIGINT(20) UNSIGNED NOT NULL,
interestmid BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY(targetmsid, interestmsid)
)
si.targetmsid=101, the subquery and one part of the serviceinterest primary key,
should generate a list of serviceinterests.
I want to return the one-to-one associated rstep1 (RServiceInterestVO) for each of the
those serviceinterests that were generated.
crit.addEqualTo("serviceInterest.targetMemberServiceId", 101);
crit.addEqualToField("serviceInterest.interestMemberServiceId", "memberServiceId");
Yes I am trying to join with this addEqualToField, but notice onto the 2nd half of the
primary key.
I know my mapping is wrong, bit I don't know how to give a correct mapping for this
problem. That is where my problem lies. In every other query I have done, there was
a simple one to one primary key mapping that INNER JOIN worked well on. Here, the
primary key is not fully used in the subquery and actually prevents this from working.
Hense I am asking for a new idea or direction. I don't know how to proceed. I am
trying to learn.
JohnE
----- Original Message -----
From: Jakob Braeuchi <[EMAIL PROTECTED]>
Date: Thursday, March 11, 2004 1:17 pm
Subject: Re: Still no luck with this query
> hi john,
>
> you have a 1:1-relationship pointing from RServiceInterestVO to
> ServiceInterestVO, is this correct ? a similar relationship can be
> found in the
> samples (ProductGroup to Article).
> the pk of RServiceInterestVO is used as fk to ServiceInterestVO in
> your
> repository. but the comment says it's wrong.
>
> according to the repository each class has a single pk; but the
> table
> serviceinterest uses a combined pk ? and i cannot find a create
> table for
> rstep1. pk definition in create table and the repository must
> match !
>
> crit.addEqualTo("serviceInterest.targetMemberServiceId", jMsid);
> crit.addEqualToField("serviceInterest.interestMemberServiceId",
> "memberServiceId");
> dou you try to force a join with addEqualToField ?
>
> jakob
>
> [EMAIL PROTECTED] wrote:
>
> > 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]
> >
> >
>
> -------------------------------------------------------------------
> --
> 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]
