JOIN clause with WHERE

问题:

Ive got 3 tables called Course, CourseCatogory, CourseCourseCatagory. CourseCourseCatagory is a junction table. I am trying to select courses that are linked to particular catogory using the junction table. This is my first time using a junction table and JOIN clause, I just don't know how to go about doing it. It would be highly appreciated if there's anything you could do to help me.

 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;
        }

回答1:

In order to get an understanding of how this works I suggest you start out like:

FROM Course c -- or with CourseCategory

then JOIN the junction table:

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

then join CATEGORY with the junction table:

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

Then add the where clause:

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

I assume that Category is an attribute of CourseCategory (always use a qualifier since it will make the code easier to read and understand). Finally, select from the relation you defined:

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