JOIN与WHERE子句(JOIN clause with WHERE)

Ive得到了3个表叫场，CourseCatogory，CourseCourseCatagory。 CourseCourseCatagory是结合表。我想选择使用结合表链接到特定catogory课程。这是我第一次使用接线表和JOIN子句，我只是不知道如何去这样做。它将如果有什么你可以做些什么来帮助我高度赞赏。

 public static courseDetails getSelectedCatogory(string Category)
        {
              courseDetails details = new courseDetails();

            using (OleDbConnection connection = getConnection())
            {
                string query = "SELECT CourseName, Description FROM CourseCourseCategory WHERE Category = @Category JOIN Course ON Course.ID = CourseCourseCategory.CourseID " +
                    "JOIN CourseCategory ON CourseCategory.ID = CourseCourseCategory.CourseCategoryID";

                OleDbCommand command = new OleDbCommand(query, connection);
                OleDbParameter pram = new OleDbParameter("@CourseCategory", CourseCategory);
                command.Parameters.Add(pram);
                connection.Open();

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {

                  courseDetails d = new courseDetails();

                  d.CourseName = reader["CourseName"].ToString();
                  d.Description = reader["Description"].ToString();

                    details = d;
                }



            }

            return details;
        }

为了得到这是如何工作，我建议你开始像一个认识：

FROM Course c -- or with CourseCategory

再加入结表：

FROM Course c
JOIN CourseCourseCategory cc
    ON c.ID = cc.CourseID

再加入与接线表类别：

FROM Course c
JOIN CourseCourseCategory ccc
    ON c.ID = cc.CourseID
JOIN CourseCategory cc
    ON cc.ID = ccc.CourseCategoryID

然后添加where子句：

FROM Course c
JOIN CourseCourseCategory ccc
    ON c.ID = cc.CourseID
JOIN CourseCategory cc
    ON cc.ID = ccc.CourseCategoryID
WHERE cc.Category = @Category

我认为是类（总是用一个限定词，因为这将使得代码更易于阅读和理解）CourseCategory的属性。最后，从你所定义的关系中进行选择：

SELECT c.CourseName, c.Description
FROM Course c
JOIN CourseCourseCategory ccc
    ON c.ID = cc.CourseID
JOIN CourseCategory cc
    ON cc.ID = ccc.CourseCategoryID
WHERE cc.Category = @Category