从两个带联接的表中获取两个外键值

Question

我下面有桌子；

Course(cname, ccode, credit, dept) // course table.
Section(sno, ccode, semestr, year, prof); // course and lectures according to year and semester value.
Prerequisite(ccode, precode) // prerequisite for any course.

问题：

列出2012年开设的课程和必修课--两者的代码和名称

我试图通过加入来解决这个问题。

select c.ccode, c.cname, p.precode from Course as c 
inner join prerequisite as p on p.ccode=c.ccode;

查询返回下面的元组。

Course | Name                 | Pre. code  
B201   | software engineering | B101  
B202   | operating system     | B101  
H202   | civil law            | H102

我怎样才能得到必修课的名称和他们的代码？预期的结果必须在以下；

Course    | Name                 | Pre. code  | Pre. name
B201      | software engineering | B101       | aaaaa   
B202      | operating system     | B101       | aaaaa 
H202      | civil law            | H102       | bbbb

Answer 1

我使用您的查询，并添加一个EXISTS子句。基本上，逻辑在EXISTS条款中，我发现了2012年所有有章节的课程。然后，EXISTS将只返回与EXISTS子句匹配的课程。

SELECT c.ccode, c.cname, p.precode 
FROM Course c 
JOIN prerequisite p ON p.ccode = c.ccode
WHERE EXISTS (
    SELECT 1
    FROM Section
    WHERE ccode = c.ccode AND year = '2012'
)

或者如果使用JOIN，

SELECT c.ccode, c.cname, p.precode, cp.cname
FROM Course c 
JOIN prerequisite p ON p.ccode = c.ccode
JOIN Course cp ON cp.ccode = p.precode
JOIN Section s ON s.ccode = c.ccode
WHERE s.year = '2012'

Answer 2

select c.ccode, c.cname
     , p.precode, cp.name as preName 
  from Course as c 
  join prerequisite as p 
    on p.ccode = c.ccode 
  join Cource cp 
    on cp.ccode = p.precode