Tag Archives: Column ‘id’ in field list is ambiguous

[Solved] Mybatis multi-table query error: Column ‘id’ in field list is ambiguous

Leave a reply

Mybatis error example:


<resultMap id="JoinResultMap" type="com.WorkDto">
	<id column="id" jdbcType="BIGINT" property="id"/>
	<result column="work_city_code" jdbcType="VARCHAR" property="workCityCode"/>

	
	<collection property="guardInfos" ofType="com.GuardInfo">
		<id column="id"  jdbcType="BIGINT" property="id"  />
		<result column="work_id" jdbcType="BIGINT" property="workId" />
		<result column="guarder_code" jdbcType="VARCHAR" property="guarderCode" />
	</collection>
</resultMap>


<select id="selectById" parameterType="java.lang.Long" resultMap="JoinResultMap">
	select t1.id, work_city_code, 
	t2.id , t2.work_id, t2.guarder_code 
	from tt_work t1
	left join tt_work_info t2 
	on t1.id=t2.work_id
	where id = #{id,jdbcType=BIGINT}
</select>

The above will report an error: Column ‘id’ in field list is ambiguous

wrong reason:

When Mybatis multi-table query, multiple tables have fields with the same name, such as id, the names are repeated, and the corresponding table name is not specified.
There are two places to pay attention to:
(1) Change the column of Mybatis in one of the repeated fields to another name.
(2) Field plus the corresponding table name.

amend as below:

The following will
(1) modify the column corresponding to one of the ids to other unique names guard_info_id
(2) add the table name t1.id to the id in the query results and query conditions

<resultMap id="JoinResultMap" type="com.WorkDto">
	<id column="id" jdbcType="BIGINT" property="id"/>
	<result column="work_city_code" jdbcType="VARCHAR" property="workCityCode"/>

	<collection property="guardInfos" ofType="com.GuardInfo">
		<id column="guarder_info_id"  jdbcType="BIGINT" property="id"  />
		<result column="work_id" jdbcType="BIGINT" property="workId" />
		<result column="guarder_code" jdbcType="VARCHAR" property="guarderCode" />
	</collection>
</resultMap>


<select id="selectById" parameterType="java.lang.Long" resultMap="JoinResultMap">
	select t1.id,  work_city_code, 
	t2.id as guarder_info_id, t2.work_id, t2.guarder_code 
	from tt_work t1
	left join tt_work_info t2 
	on t1.id=t2.work_id
	where t1.id = #{id,jdbcType=BIGINT}
</select>

MYSQL error: Column’id’ in field list is ambiguous solution

[Err] 1052 – Column ‘modify_time’ in where clause is ambiguous

Error statement:

SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59′
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0

) T

 

The column’ID’ is repeated in the field list. In fact, the two tables have the same field, but the table name is not added before the name of the table field when used, resulting in unknown reference

 

after adjustment:

SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,xes_appeals.modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0

) T