title: association query issue
body: <p>I'm using mybatis , I encounter a association query issue, pls take a look at table structures first.</p>
  
  <pre><code>DROP TABLE IF EXISTS `comp_items_spec`;
  
  CREATE TABLE `comp_items_spec` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `comp_id` int(11) NOT NULL,
    `content_type_id` int(11) NOT NULL,
    `in_list_flag` char(1) DEFAULT NULL,
    `label` varchar(200) DEFAULT NULL,
    `static_flag` char(1) DEFAULT NULL,
    `ext1_name` varchar(200) DEFAULT NULL,
    `ext1_value` text,
    `ext2_name` varchar(200) DEFAULT NULL,
    `ext2_value` text,
    `ext3_name` varchar(200) DEFAULT NULL,
    `ext3_value` text,
    `ext4_name` varchar(200) DEFAULT NULL,
    `ext4_value` text,
    `ext5_name` varchar(200) DEFAULT NULL,
    `ext5_value` text,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;
  
  /*Data for the table `comp_items_spec` */
  
  insert  into `comp_items_spec`(`id`,`comp_id`,`content_type_id`,`in_list_flag`,`label`,`static_flag`,`ext1_name`,`ext1_value`,`ext2_name`,`ext2_value`,`ext3_name`,`ext3_value`,`ext4_name`,`ext4_value`,`ext5_name`,`ext5_value`) values (43,22,1,'\0','description','Y','description','description1......',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(44,22,3,'\0','static image','Y',NULL,'http://img3.cache.netease.com/cnews/2012/12/6/20121206092637ba11c.jpg',NULL,'501',NULL,'425',NULL,NULL,NULL,NULL);
  
  /*Table structure for table `components_spec` */
  
  DROP TABLE IF EXISTS `components_spec`;
  
  CREATE TABLE `components_spec` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(200) DEFAULT NULL,
    `show_latest_num` int(11) DEFAULT NULL,
    `more_link_url` varchar(200) DEFAULT NULL,
    `more_link_flag` char(1) DEFAULT NULL,
    `open_in_new_flag` char(1) DEFAULT NULL,
    `create_date` datetime DEFAULT NULL,
    `update_date` datetime DEFAULT NULL,
    `creator_sso` varchar(20) DEFAULT NULL,
    `updator_sso` varchar(20) DEFAULT NULL,
    KEY `id` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
  
  /*Data for the table `components_spec` */
  
  insert  into `components_spec`(`id`,`name`,`show_latest_num`,`more_link_url`,`more_link_flag`,`open_in_new_flag`,`create_date`,`update_date`,`creator_sso`,`updator_sso`) values (22,'Banner',5,'more.blog.ge.com','Y','Y','2012-12-08 21:30:58','2012-12-08 21:30:58','502156886','502156886');
  </code></pre>
  
  <p>the relationship between components_spec and comp_items_spec is 1:N,comp_items_spec has two kind of data ,dynamic items and static items which are distinguish from column static_flag, it's dynamic items when static_flag='N', and I also define bean ComponentSpec and CompItemSpec to map the entity. pls see the source code from below:</p>
  
  <pre><code>import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  
  public class ComponentSpec {
      private int id;
      private String name;
      private int showLatestNum;
      private String moreLinkUrl;
      private char moreLinkFlag;
      private char openInNewFlag;
      private Date createDate;
      private Date updateDate;
      private String creatorSSO;
      private String updatorSSO;
      private List&lt;CompItemSpec&gt; staticItemSpecList =new ArrayList&lt;CompItemSpec&gt;();
      private List&lt;CompItemSpec&gt; dynamicItemSpecList =new ArrayList&lt;CompItemSpec&gt;();
  
      public Date getCreateDate() {
          return createDate;
      }
  
      public void setCreateDate(Date createDate) {
          this.createDate = createDate;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public char getMoreLinkFlag() {
          return moreLinkFlag;
      }
  
      public void setMoreLinkFlag(char moreLinkFlag) {
          this.moreLinkFlag = moreLinkFlag;
      }
  
      public String getMoreLinkUrl() {
          return moreLinkUrl;
      }
  
      public void setMoreLinkUrl(String moreLinkUrl) {
          this.moreLinkUrl = moreLinkUrl;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public int getShowLatestNum() {
          return showLatestNum;
      }
  
      public void setShowLatestNum(int showLatestNum) {
          this.showLatestNum = showLatestNum;
      }
  
      public Date getUpdateDate() {
          return updateDate;
      }
  
      public void setUpdateDate(Date updateDate) {
          this.updateDate = updateDate;
      }
  
      public char getOpenInNewFlag() {
          return openInNewFlag;
      }
  
      public void setOpenInNewFlag(char openInNewFlag) {
          this.openInNewFlag = openInNewFlag;
      }
  
      public List&lt;CompItemSpec&gt; getStaticItemSpecList() {
          return staticItemSpecList;
      }
  
      public void addStaticItemSpec(CompItemSpec compStaticItemSpec){
          getStaticItemSpecList().add(compStaticItemSpec);
          compStaticItemSpec.setComponentSpec(this);
      }
  
      public List&lt;CompItemSpec&gt; getDynamicItemSpecList() {
          return dynamicItemSpecList;
      }
  
      public void addDynamicItemSpec(CompItemSpec dynamicItemSpec){
          getDynamicItemSpecList().add(dynamicItemSpec);
          dynamicItemSpec.setComponentSpec(this);
      }
  
      public String getCreatorSSO() {
          return creatorSSO;
      }
  
      public void setCreatorSSO(String creatorSSO) {
          this.creatorSSO = creatorSSO;
      }
  
      public String getUpdatorSSO() {
          return updatorSSO;
      }
  
      public void setUpdatorSSO(String updatorSSO) {
          this.updatorSSO = updatorSSO;
      }
  
  
  }
  
  public class CompItemSpec {
      private int id;
      private int compId;
      private int contentTypeId;
      private char inListFlag;
      private char staticFlag;
      private String label;
      private String ext1Name;
      private String ext1Value;
      private String ext2Name;
      private String ext2Value;
      private String ext3Name;
      private String ext3Value;
      private String ext4Name;
      private String ext4Value;
      private String ext5Name;
      private String ext5Value;
      private ComponentSpec componentSpec;
  
      public int getCompId() {
          return compId;
      }
  
      public void setCompId(int compId) {
          this.compId = compId;
      }
  
      public String getExt1Name() {
          return ext1Name;
      }
  
      public void setExt1Name(String ext1Name) {
          this.ext1Name = ext1Name;
      }
  
      public String getExt1Value() {
          return ext1Value;
      }
  
      public void setExt1Value(String ext1Value) {
          this.ext1Value = ext1Value;
      }
  
      public String getExt2Name() {
          return ext2Name;
      }
  
      public void setExt2Name(String ext2Name) {
          this.ext2Name = ext2Name;
      }
  
      public String getExt2Value() {
          return ext2Value;
      }
  
      public void setExt2Value(String ext2Value) {
          this.ext2Value = ext2Value;
      }
  
      public String getExt3Name() {
          return ext3Name;
      }
  
      public void setExt3Name(String ext3Name) {
          this.ext3Name = ext3Name;
      }
  
      public String getExt3Value() {
          return ext3Value;
      }
  
      public void setExt3Value(String ext3Value) {
          this.ext3Value = ext3Value;
      }
  
      public String getExt4Name() {
          return ext4Name;
      }
  
      public void setExt4Name(String ext4Name) {
          this.ext4Name = ext4Name;
      }
  
      public String getExt4Value() {
          return ext4Value;
      }
  
      public void setExt4Value(String ext4Value) {
          this.ext4Value = ext4Value;
      }
  
      public String getExt5Name() {
          return ext5Name;
      }
  
      public void setExt5Name(String ext5Name) {
          this.ext5Name = ext5Name;
      }
  
      public String getExt5Value() {
          return ext5Value;
      }
  
      public void setExt5Value(String ext5Value) {
          this.ext5Value = ext5Value;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public int getContentTypeId() {
          return contentTypeId;
      }
  
      public void setContentTypeId(int contentTypeId) {
          this.contentTypeId = contentTypeId;
      }
  
      public char getInListFlag() {
          return inListFlag;
      }
  
      public void setInListFlag(char inListFlag) {
          this.inListFlag = inListFlag;
      }
  
      public char getStaticFlag() {
          return staticFlag;
      }
  
      public void setStaticFlag(char staticFlag) {
          this.staticFlag = staticFlag;
      }
  
      public String getLabel() {
          return label;
      }
  
      public void setLabel(String label) {
          this.label = label;
      }
  
      public ComponentSpec getComponentSpec() {
          return componentSpec;
      }
  
      public void setComponentSpec(ComponentSpec componentSpec) {
          this.componentSpec = componentSpec;
      }
  }
  </code></pre>
  
  <p>sqlmap is like below:</p>
  
  <pre><code>    &lt;mapper namespace="com.ge.dao.ComponentSpecMapper"&gt;
  &lt;resultMap id="componentMap" type="componentSpec"&gt;
      &lt;id column="id" property="id"/&gt;
      &lt;result column="temp_id" property="tempId"/&gt;
      &lt;result column="show_latest_num" property="showLatestNum"/&gt;
      &lt;result column="more_link_url" property="moreLinkUrl"/&gt;
      &lt;result column="more_link_flag" property="moreLinkFlag"/&gt;
      &lt;result column="open_in_new_flag" property="openInNewFlag"/&gt;
      &lt;result column="update_date" property="updateDate"/&gt;
      &lt;result column="create_date" property="createDate"/&gt;
      &lt;result column="updator_sso" property="updatorSSO"/&gt;
      &lt;result column="creator_sso" property="creatorSSO"/&gt;
      &lt;collection property="staticItemSpecList" ofType="itemSpec"&gt;
          &lt;id column="static_item_id" property="id"/&gt;
          &lt;result column="id" property="compId"/&gt;
          &lt;result column="static_content_type_id" property="contentTypeId"/&gt;
          &lt;result column="static_label" property="label"/&gt;
          &lt;result column="static_ext1_value" property="ext1Value"/&gt;
          &lt;result column="static_ext2_value" property="ext2Value"/&gt;
          &lt;result column="static_ext3_value" property="ext3Value"/&gt;
          &lt;result column="static_ext4_value" property="ext4Value"/&gt;
          &lt;result column="static_ext5_value" property="ext5Value"/&gt;
      &lt;/collection&gt;
      &lt;collection property="dynamicItemSpecList" ofType="itemSpec"&gt;
          &lt;id column="dynamic_item_id" property="id"/&gt;
          &lt;result column="id" property="compId"/&gt;
          &lt;result column="dynamic_content_type_id" property="contentTypeId"/&gt;
          &lt;result column="dynamic_in_list_flag" property="inListFlag"/&gt;
          &lt;result column="dynamic_label" property="label"/&gt;
          &lt;result column="dynamic_ext1_value" property="ext1Value"/&gt;
          &lt;result column="dynamic_ext2_value" property="ext2Value"/&gt;
          &lt;result column="dynamic_ext3_value" property="ext3Value"/&gt;
          &lt;result column="dynamic_ext4_value" property="ext4Value"/&gt;
          &lt;result column="dynamic_ext5_value" property="ext5Value"/&gt;
      &lt;/collection&gt;
  
  &lt;/resultMap&gt;
  
  &lt;sql id="compSpecMain"&gt;
        SELECT
            comp.id,
            comp.name,
            show_latest_num,
            more_link_url,
            more_link_flag,
            open_in_new_flag,
            create_date,
            update_date,
            creator_sso,
            updator_sso,
            si.id static_item_id,
            si.content_type_id static_content_type_id,
            si.in_list_flag static_in_list_flag,
            si.label static_label,
            si.ext1_value static_ext1_value,
            si.ext2_value static_ext2_value,
            si.ext3_value static_ext3_value,
            si.ext4_value static_ext4_value,
            si.ext5_value static_ext5_value,
            di.id dynamic_item_id,
            di.content_type_id dynamic_content_type_id,
            di.in_list_flag dynamic_in_list_flag,
            di.label dynamic_label,
            di.ext1_value dynamic_ext1_value,
            di.ext2_value dynamic_ext2_value,
            di.ext3_value dynamic_ext3_value,
            di.ext4_value dynamic_ext4_value,
            di.ext5_value dynamic_ext5_value
          FROM components_spec comp
              LEFT JOIN comp_items_spec si ON si.comp_id=comp.id AND si.static_flag='Y'
              LEFT JOIN comp_items_spec di ON di.comp_id=comp.id AND di.static_flag='N'
  &lt;/sql&gt;
  
  &lt;select id="selectComponentSpecByID" parameterType="int" resultMap="componentMap"&gt;
          &lt;include refid="compSpecMain"/&gt;
          WHERE
              comp.id=#{id}
  &lt;/select&gt;
  
   &lt;select id="selectComponentSpecByName" parameterType="string" resultMap="componentMap"&gt;
          &lt;include refid="compSpecMain"/&gt;
          WHERE
              comp.name like #{name}
  
  &lt;/select&gt;
  </code></pre>
  
  <p></p>
  
  <p>the issue here is when I do the selectComponentSpecByID query, the dynamic item list is supposed has nothing , but actual result is two elements, I think it's a problem with result map definition, but no glue. any suggestions? thanks .</p>