RE: Problem with fetching data in one to many relationship

[Randy Layman]

 
    If there are no entries in table Child then the SQL statement will
not return any records.  To return the records from Parent with no
children, you need to use a LEFT OUTER JOIN.
 
    I would suggest using your tool's SQL interpreter to get the query
right before trying to incorporate that into iBATIS.
 
    Randy
 
 

________________________________

From: Band, Aniket [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 18, 2007 5:12 PM
To: user-java@ibatis.apache.org
Subject: RE: Problem with fetching data in one to many relationship


but it works fine when there are child records. You mean to say that if
I remove CDATA then query will fetch the result even when child is not
present?

        -----Original Message-----
        From: Poitras Christian [mailto:[EMAIL PROTECTED]
        Sent: Thursday, January 18, 2007 5:09 PM
        To: user-java@ibatis.apache.org
        Subject: RE: Problem with fetching data in one to many
relationship
        
        
        Remove CDATA in second request. The CDATA prevents your second
request from working.
        Or better, remove it everywhere and use > and < instead of
> and <.
         
        Christian

________________________________

        From: Band, Aniket [mailto:[EMAIL PROTECTED] 
        Sent: Thursday, 18 January 2007 16:49
        To: user-java@ibatis.apache.org
        Subject: Problem with fetching data in one to many relationship
        
        

        Hi 
                I am having problem with fetching data using ibatis.
When there are children to a parent, parent also does not get returned.

        Just to give you an  idea queries are something like this 

           <resultMap id="parentList" class="com.example.parentDto"> 
            <result property="id" column="PARENT_ID" columnIndex="1"/> 
            <result property="description" column="DESCRIPTION"
columnIndex="2"/>    
                <result property="childList" column="PARENT_ID"
select="getChildrenList"/> 
          </resultMap> 

          <resultMap id="childrenList" class="com.example.childDto"> 
                <result property="Id" column="child_ID"/>       
                <result property="frenchName" column="FRENCH_NAME"/> 
                <result property="name" column="NAME"/> 
          </resultMap> 

        <statement id="getParentList" resultMap="parentList"
parameterClass="java.util.Map"><![CDATA[ 
                                SELECT 
                                        id, 
                                        description 
                                FROM 
                                        Parent p 
                                where           
                                        p.EFFECTIVE_DATE <= SYSDATE 
                                        AND p.END_DATE > SYSDATE 
          ]]> </statement> 
          
          <statement id="getChildrenList" resultMap="childrenList"> 
                  <![CDATA[ SELECT 
                                        c.ID 
                                        c.FRENCH_NAME, 
                                        c.NAME 
                                 FROM 
                                        Parent p, 
                                        Child c 
                                WHERE 
                                        p.ID = c.Parent_id 
                                        AND c.EFFECTIVE_DATE <= SYSDATE 
                                        AND c.END_DATE > SYSDATE 
                                        AND c.PARENT_ID = #value#  
         ]]> </statement> 


        Any immediate help will be highly appreciated. 

        Regards, 
        Aniket