MyBatis - How to join multiple columns from same table

Question

I'm trying to learn how to join multiple columns from one table to a single column from another table, for a given Id.

Here is my team table:

create table teams ( 
  id varchar(10),
  name varchar(30),
  primary key (id)
);

Here is my teammatch table:

create table teammatch (
  id integer,
  firstTeamId varchar,
  secondTeamId varchar,
  matchId integer,
  primary key(id),
  foreign key (firstTeamId) references teams (id),
  foreign key (secondTeamId) references teams (id)
);

My sql:

select teammatch.*, t1.*, t2.*
    from teammatch
    inner join teams t1 on teammatch.firstTeamId = t1.id
    inner join teams t2 on teammatch.secondTeamId = t2.id
    where teammatch.id = #{id}

Data:

ID      FIRSTTEAMID     SECONDTEAMID 
1       POR             DEN 
2       TOR             PHI 

This query returns POR team for both t1 and t2 but I need it to return POR for t1 and DEN for t2

EDIT: This works fine when I write sql query in H2 database, but when I write it with mybatis in xml mapper file, It returns same value for both fields.

Here is my ResultMap

<resultMap id="TeamMatchMap" type="TeamMatch">
    <id column="id" property="id" />

    <association property="firstTeamId" column="firstTeamId" javaType="Team">
      <id column="id" property="id" />
      <result column="name" property="name" />
    </association>

    <association property="secondTeamId" column="secondTeamId" javaType="Team">
      <id column="id" property="id" />
      <result column="name" property="name" />
    </association>
  </resultMap>

Answer 1

pleasy try to change your mapper to something like this

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper>
    <select id="getTeammatches"
            resultMap="teamMatchMap">
        select
            teammatch.id as matchid       ,
            t1.id        as firstteam_id  ,
            t1.name      as firstteam_name,
            t2.id        as secondteam_id ,
            t2.name      as secondteam_name
          from teammatch
            inner join teams t1 on teammatch.firstTeamId = t1.id
            inner join teams t2 on teammatch.secondTeamId = t2.id
          where teammatch.id = #{id}
    </select>
    <resultMap type="TeamMatch" id="teamMatchMap">
        <result property="id" column="MATCHID"/>
        <association property="team1" columnPrefix="FIRSTTEAM_" resultMap="teamMap"/>
        <association property="team2" columnPrefix="SECONDTEAM_" resultMap="teamMap"/>
    </resultMap>
    <resultMap type="Team" id="teamMap">
        <result property="id" column="ID"/>
        <result property="name" column="NAME"/>
    </resultMap>
</mapper>

by declaring a separate resultMap for a Team or referring to an existing ones, you dont need to map columns twice in the body-tag of association-tag

you used the column-Attribute for association - as far as i know, its only used if you want myBatis to execute anoter select-tag

Edit

i wrote the mapper before i read your edit and comment

if your Team-class provides a contructor that takes id and name, your teamMap can also look like this

    <resultMap type="Team" id="teamMap">
        <constructor>
            <idArg column="ID"/>
            <arg column="NAME"/>
        </constructor>
    </resultMap>