Parent child join query

[Raaj Aaryan]

Hi,

I have a parent table and a child table with one-to-many relationship. Each parent record is related to one or more child records.

For example each record in PEOPLE (parent table) has one or more records in PHONE (child tables).

I have an application which generates an XML file from the result object returned from the PEOPLE table. The structure of the output is like

<people>
<name>...</name>
<phone>
<phoneNumber>...</phoneNumber>
<phoneNumber>...</phoneNumber>
</phone>
</people>

If I run a simple query on the PEOPLE table due to the association with the PHONE table, it runs a series of associated select on the child tables. To avoid this I make use of

left join fetch

from the parent table onto the child table. But when I run this query I get duplicated entries for each record which has more than one phone number.

I think I have to make use of tuple queries to remove this duplication in results, but not sure how to do it. Can anyone suggest me the approach to do it.

thanks in advance

[Mark Spritzler]

Please post your mapping files, and your code for the query.

Mark

[Raaj Aaryan]

Here is the mapping file of the parent table

   <hibernate-mapping>
    <class name="Parent" table="PARENT_TABLE">
        <id name="parentId" type="long">
            <column name="PARENT_ID" precision="22" scale="0" />
            <generator class="sequence" >
            <param name="sequence">parent_seq</param>
            </generator>
        </id>
<many-to-one name="child1" class="Child1" fetch="select" lazy="no-proxy">
            <column name="CHILD1_ID" precision="22" scale="0" />
        </many-to-one>
        <property name="name" type="string">
            <column name="NAME" length="1" />
        </property>
.
.
.
        <set name="Child2Set" inverse="true" lazy="true" cascade="all" outer-join="true">
            <key>
                <column name="PARENT_ID" precision="22" scale="0" not-null="true" />
            </key>
<one-to-many class="Child2" />
        </set>
    </class>
</hibernate-mapping> 

and the query is

  from Parent parent left join fetch parent.child1 left join fetch parent.Child2Set  

I have tried to remove these duplications using a tuple query but I am still curious to solve my earlier problem