将具有相同条件的2个SELECT语句连接起来

Question

我有两个SELECT语句：

set @student_id = (select ID from student where student.name='A');
SELECT course_id, title, credits
FROM course
WHERE course_id IN (SELECT course_id FROM takes 
WHERE takes.ID = @student_id AND grade != 'F');
SELECT course_id, grade
FROM takes
WHERE course_id IN (SELECT course_id FROM takes 
WHERE takes.ID = @student_id AND grade != 'F');

由于results的公共列是course_id，所以我尝试按以下方式加入它们，但得到了错误的结果。

SELECT DISTINCT course.course_id, course.title, course.credits, takes.grade
FROM course INNER JOIN takes on course.course_id = takes.course_id
WHERE course.course_id IN (SELECT course_id FROM takes 
WHERE takes.ID = @student_id AND grade != 'F');```
Any help?

Answer 1

SELECT DISTINCT course.course_id, course.title, course.credits, takes.grade
FROM course JOIN takes on course.course_id = takes.course_id
WHERE  takes.ID = @student_id AND takes.grade != 'F'