时间戳

首页 > 解决方案 > 错误代码 1066 sqlstate 42000 不是唯一的表别名

mysql - 错误代码 1066 sqlstate 42000 不是唯一的表别名

问题描述

我收到此错误调用不是唯一的表别名，我无法找出问题所在。我有一个连接所有的通用日期表。还有教师表连接两个表 - 离开和出勤。请帮忙 

    SELECT
    trns_teacherattendance.Attendance_Status,
    trns_teacherattendance.Attendance_Month,
    trns_teacherattendance.AcademicYear_Id,
    trns_teacherattendance.School_Id,
    trns_teacherattendance.Bio_Code,
    trns_teacherattendance.IsActive,
    mst_holiday_teacher.Holiday_Name,
    dates.dates,
    leave_new_view_teacher.Leave_Status,
    mst_teacher.Teacher_Name,
    leave_new_view_teacher.LeaveDate
FROM
    trns_teacherattendancemapping
LEFT OUTER JOIN
    trns_teacherattendance
ON
    (
        trns_teacherattendancemapping.Bio_Code =
        trns_teacherattendance.Bio_Code)
RIGHT OUTER JOIN
    dates
ON
    (
        trns_teacherattendance.Attendance_Date = dates.dates)
LEFT OUTER JOIN
    mst_teacher
ON
    (
        trns_teacherattendancemapping.Teacher_Id =
        mst_teacher.Teacher_Id)
RIGHT OUTER JOIN
    leave_new_view_teacher
ON
    (
        mst_teacher.Teacher_Id = leave_new_view_teacher.TID)
LEFT OUTER JOIN
    leave_new_view_teacher
ON
    (
        dates.dates = leave_new_view_teacher.LeaveDate)
LEFT OUTER JOIN
    mst_holiday_teacher
ON
    (
        dates.dates = mst_holiday_teacher.Holiday_Date) ;

标签： mysql

解决方案

您将两次加入一个表leave_new_view_teacher，但您没有给它不同的别名。数据库无法知道您想从哪个查询/表中引用您的列。每个连接都可以提取不同的数据/行。

作为本部分的最低要求，为这些表分配别名：

RIGHT OUTER JOIN
    leave_new_view_teacher AS lnvt1 -- here
ON
    mst_teacher.Teacher_Id = leave_new_view_teacher.TID
LEFT OUTER JOIN
    leave_new_view_teacher AS lnvt2 -- and here
ON
    dates.dates = leave_new_view_teacher.LeaveDate

您还需要对列进行SELECT部分正确分类：

SELECT
  ...
  lnvt1.Leave_Status, -- you're probably getting this from first join
  mst_teacher.Teacher_Name,
  lnvt2.LeaveDate -- this probably comes from second join

您也不需要每个ON子句的括号，所以我删除了它们。

推荐阅读