我怎样才能找到这个查询

I want to select student Amy's course_id with teacher id.

我想用老师id选择学生Amy的course_id。

How can I do that?

我怎样才能做到这一点?

Student table:

| ID      | name      | dept_name      | tot_cred      |
| S2905   | Lily      | Elec.Eng.      | 54            |
| S2906   | Ella      | Elec.Eng.      | 32            |
| S3901   | Amy       | Music          | 23            |

Takes table:

| ID      | course_id| sec_id| semester      | year      | grade      |
| S3901   | HIS-351  | 1     | Spring  | 2010      | A  |
| S3901   | MTH-101  | 1   | Fall    | 2009      | B- |
| S3901   | MU-101   | 1   | Spring  | 2009      | A  |
| S3901   | MU-199   |1    | Spring  | 2010      | A- |
| S3902   | HIS-35   | 1   | Spring  | 2010      | B  |
| S3902   | MTH-101  | 1   | Fall    | 2009      | B+ |
| S3902   | MU-101   | 1   | Spring  | 2009      | A  |

Teaches table

| ID      | course_id      | sec_id      | semester      | year      |
| 76766   | BIO-101        | 1           | Summer        | 2009      |
| 76766   | BIO-301        | 1           | Summer        | 2010      |
| 10101   | CS-101         | 1           | Fall          | 2009      |
| 45565   | CS-101         | 1           | Spring        | 2010      |
| 83821   | CS-190         | 1           | Spring        | 2009      |

1 个解决方案

#1

Hi I would recommend you do some reading on writing sql as this is a very basic query.

嗨,我建议你做一些关于编写sql的阅读,因为这是一个非常基本的查询。

MSDN is a great source of information on all of SQL SERVER and the web is full of articles to learn, such as the stairway to articles on http://www.sqlservercentral.com/stairway/

MSDN是所有SQL SERVER的重要信息来源,网上有很多要学习的文章,比如http://www.sqlservercentral.com/stairway/上的文章的阶梯。

The bellow should do what you want.

波纹管应该做你想要的。

SELECT t.course_id
       ,te.Id
FROM Student s
INNER JOIN Takes t
    ON t.id = s.id
INNER JOIN Teaches te
    ON te.course_id = t.course_id
WHERE s.name = 'amy'

#1