加入 3 个表时 SQL 无效的对象名称

Tran 发表于 Dev

陈

我被要求找出他们至少教过两次的所有课程的老师姓名和职称。

我的数据库：

create table teaches
(ID         varchar(5), 
 course_id      varchar(8),
 sec_id         varchar(8), 
 semester       varchar(6),
 year           numeric(4,0),
 primary key (ID, course_id, sec_id, semester, year),
 foreign key (course_id,sec_id, semester, year) references section
    on delete cascade,
 foreign key (ID) references instructor
    on delete cascade
);
create table course
(course_id      varchar(8), 
 title          varchar(50), 
 dept_name      varchar(20),
 credits        numeric(2,0) check (credits > 0),
 primary key (course_id),
 foreign key (dept_name) references department
    on delete set null
);

create table instructor
(ID         varchar(5), 
 name           varchar(20) not null, 
 dept_name      varchar(20), 
 salary         numeric(8,2) check (salary > 29000),
 primary key (ID),
 foreign key (dept_name) references department
    on delete set null
);`

我的代码是：

 SELECT DISTINCT instructor.name, course.title 
FROM    teaches join instructor
            ON teaches.ID = instructor.ID
        join course 
            ON teaches.course_id = course.course_id
WHERE       count(teaches.course_id) > 1
GROUP BY    instructor.name;

它返回错误，指出“无效的对象名称‘教学’。你能告诉我我的代码有什么问题吗？

阿比舍克VK

尝试这个

SELECT i.name, c.title ,count(*)
FROM    teaches t
    join 
    course c
        ON t.course_id = c.course_id
    join instructor i 
        ON t.ID=i.ID
GROUP BY    i.name,c.title
having count(*)>1
;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。