时间戳

首页 > 解决方案 > 更正我的 SQL 查询中的相关名称

sql-server - 更正我的 SQL 查询中的相关名称

问题描述

我有以下 SQL 查询，我想从我的 SQL 数据库中提取每个项目的计数及其名称，例如“FillingOption”。

我不断收到以下错误消息

Msg 1013, Level 16, State 1, Line 13 FROM 子句中的对象“school_to_classroom_teacher”和“school_to_classroom_teacher”具有相同的公开名称。使用相关名称来区分它们。

查询如下：

SELECT 
    COUNT(*) AS count,   
    Monday_Sandwich_Roll_Filling.FillingOption
FROM  
    #tempCustomer 
    INNER JOIN Monday_Sandwich_Roll_Filling 
        ON #tempCustomer.LunchMon=Monday_Sandwich_Roll_Filling.FillingOption 
    INNER JOIN school_to_classroom_teacher 
        ON #tempCustomer.teacherID= school_to_classroom_teacher.teacherID 
    INNER JOIN [teacherClassroom Table] 
        ON #tempCustomer.teacherID= [teacherClassroom Table].teacherID 
    inner join school_to_classroom_teacher 
        on [teacherClassroom Table].teacherID=school_to_classroom_teacher.teacherID 
    inner join SchoolTable 
        on SchoolTable.SchoolID 
WHERE
    (SchoolTable.mondayOn = 1)  
    AND school_to_classroom_teacher.schoolid = 1 
    AND([teacherClassroom Table].Available = 1)```

为凌乱的查询道歉，但它不喜欢WHERE 子句旁边的“ SchoolTable.SchoolID ”和查询的“ school_to_classroom_teacher ”部分。

我试过声明SchoolTable.SchoolID AS TEST，但这不起作用。

有人有想法么。

标签： sql-server

解决方案

所以你基本上想要这样做（我删除了重复/多余的加入）：

SELECT 
    COUNT(*) AS count,   
    Monday_Sandwich_Roll_Filling.FillingOption
FROM  
    #tempCustomer 
    INNER JOIN Monday_Sandwich_Roll_Filling 
        ON #tempCustomer.LunchMon=Monday_Sandwich_Roll_Filling.FillingOption 
    INNER JOIN school_to_classroom_teacher 
        ON #tempCustomer.teacherID= school_to_classroom_teacher.teacherID 
    INNER JOIN [teacherClassroom Table] 
        ON #tempCustomer.teacherID= [teacherClassroom Table].teacherID 
        and [teacherClassroom Table].teacherID=school_to_classroom_teacher.teacherID 
    inner join SchoolTable 
        on SchoolTable.SchoolID 
WHERE
    (SchoolTable.mondayOn = 1)  
    AND school_to_classroom_teacher.schoolid = 1 
    AND([teacherClassroom Table].Available = 1)

但是您上次加入时也缺少加入条件inner join SchoolTable on SchoolTable.SchoolID.....？

推荐阅读