使用内部联接和具有
使用内部联接和具有
提问于 2014-02-01 18:55:20
这是我的桌子：
mysql> select * from professor;
+-------+--------+--------+--------+------+
| empid | name | status | salary | age |
+-------+--------+--------+--------+------+
| 1 | Arun | 1 | 2000 | 23 |
| 2 | Benoy | 0 | 3000 | 25 |
| 3 | Chacko | 1 | 1000 | 36 |
| 4 | Divin | 0 | 5000 | 32 |
| 5 | Edwin | 1 | 2500 | 55 |
| 7 | George | 0 | 1500 | 46 |
+-------+--------+--------+--------+------+
6 rows in set (0.00 sec)
mysql> select * from works;
+----------+-------+---------+
| courseid | empid | classid |
+----------+-------+---------+
| 1 | 1 | 10 |
| 2 | 2 | 9 |
| 3 | 3 | 8 |
| 4 | 4 | 10 |
| 5 | 5 | 9 |
| 6 | 1 | 9 |
| 2 | 3 | 10 |
| 2 | 1 | 7 |
| 4 | 2 | 6 |
| 2 | 4 | 6 |
| 2 | 5 | 2 |
| 7 | 5 | 6 |
| 3 | 5 | 2 |
| 6 | 4 | 10 |
| 2 | 7 | 1 |
+----------+-------+---------+
15 rows in set (0.00 sec)
mysql> select * from course;
+----------+------------+--------+
| courseid | coursename | points |
+----------+------------+--------+
| 1 | Maths | 5 |
| 2 | Science | 1 |
| 3 | English | 6 |
| 4 | Social | 4 |
| 5 | Malayalam | 20 |
| 6 | Arts | 25 |
| 7 | Biology | 20 |
+----------+------------+--------+
7 rows in set (0.00 sec)问题是：
退还所有教授教授的课程。
我尝试的查询是：
select course.coursename from
course inner join works
on course.courseid=works.empid
group by works.courseid
having works.empid in (select empid from professor);我遇到了这样的错误：
' in /ALL/ANY子查询‘中未知列'works.empid’
请帮我解决这个问题。
http://sqlfiddle.com/#!2/4b197/5
Stack Overflow用户
发布于 2014-02-01 19:00:17
试试这个：
select c.coursename, w.empid from
course as c inner join works as w
on (c.courseid=w.empid)
group by w.courseid
having w.empid in (select p.empid from professor as p);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21502135
复制相关文章
相似问题
腾讯云开发者
