JOIN子句与WHERE [英] JOIN clause with WHERE

问题描述

我有3个表，分别称为Course，CoursCatogory，CourseCourseCatagory. CourseCourseCatagory是一个联结表.我正在尝试使用联结表选择链接到特定类别的课程.这是我第一次使用联结表和JOIN子句，我只是不知道该怎么做.如果您能做些什么来帮助我，将不胜感激.

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

推荐答案

为了了解其工作原理，我建议您像这样开始:

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

FROM Course c -- or with CourseCategory

然后加入联接表:

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

然后将CATEGORY与联结表连接起来:

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

然后添加where子句:

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

我认为Category是CourseCategory的属性(始终使用限定符，因为它会使代码更易于阅读和理解).最后，从您定义的关系中选择:

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

这篇关于JOIN子句与WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！