来自4个表的复杂mysql查询

TABLE 1 - COURSE

表1 - 课程

course_id
name
category_id
status

TABLE 2 - CATEGORY

表2 - 类别

category_id
category_name

TABLE 3 - PROFESSOR

表3 - 教授

prof_id
prof_name

TABLE 4 - COURSEANDPROF (relation table of COURSES and PROFESSORS, every course can have multi professors)

表4 - COURSEANDPROF（课程和教授的关系表，每门课程可以有多位教授）

id
course_id
professor_id

I create two selects in my page to filters courses of my school, I've no problem to setup the where clause for category:

我在我的页面中创建了两个选择来过滤我学校的课程，我没有问题为类别设置where子句：

if ($_POST['category']) {
    $category = "AND courses.category_id = ".$_POST['category'];
}

select courses.*,category.name from courses INNER JOIN category ON courses.category_id = category.category_id where courses.status = 1 ".$categoria

How can I make the where on professors if every courses can have more than one professor?

如果每门课程都有一位以上的教授，我如何才能在教授的哪个位置？

ex:

例如：

Course: Learn Jquery
Category: Javascript
Professors: Kevin Smith, Sara Logan

Table courses

表课程

course_id  | name         | category_id   | status
--------------------------------------------------------
1          | Learn Jquery | 1             | 1
2          | Learn Mysql  | 3             | 0

Table category

表类别

category_id  | name     
------------------------
1            | Javascript 
3            | Database

Table professor

表教授

prof_id   | name     
------------------------
12        | Kevin Smith 
33        | Mike White
34        | Sara Logan

Table courseandprof

表courseandprof

id   | course_id  |  professor_id
-----------------------------------
12   | 1          | 12
33   | 1          | 34
34   | 2          | 33

I need to filter by category or/and by Professor.

我需要按类别或/和教授过滤。

2 个解决方案

#1

I would use this SQL code:

我会使用这个SQL代码：

SELECT courses.course_id AS course_id, courses.name AS course_name, 
    category.name AS category_name, 
    GROUP_CONCAT(DISTINCT professor.name SEPARATOR ', ') AS professor_name
FROM courses 
    LEFT JOIN category ON category.category_id = courses.category_id
    LEFT JOIN courseandprof ON courseandprof.course_id = courses.course_id
    LEFT JOIN professor ON professor.prof_id = courseandprof.professor_id
WHERE courses.status = 1
GROUP BY courses.course_id;

Beside the WHERE clause, you can put your other filters there, eg. professor.prof_id = 12, or category.category_id = 1.

除了WHERE子句，您可以将其他过滤器放在那里，例如。 professor.prof_id = 12，或category.category_id = 1。

If a course has multiple professors, this query displays one row of course and concatenated professors with ", " separator.

如果一门课程有多位教授，这个查询会显示一行课程和连接教授的“，”分隔符。

course_name: "Learn Jquery"
category_name: "Javascript"
professor_name: "Kevin Smith, Sara Logan"

Main table to query is courses. Then we left join category, courseandprof and professor tables. Multiple rows per course will be shown if the same course has been associated to multiple professors via courseandprof table. That's why we need to group it per course (using course_id), then we concatenate professor names in the SELECT clause.

要查询的主表是课程。然后我们离开了连接类别，courseandprof和教授表。如果通过courseandprof表将相同的课程与多位教授相关联，则将显示每个课程的多行。这就是为什么我们需要按程序对它进行分组（使用course_id），然后我们在SELECT子句中连接教授名称。

#2

You can create this make one request:

您可以创建此一个请求：

    SELECT c.category_id, c.category_name, co.course_id, cp.professor_id,  p.prof_name
FROM  `category` AS c
  INNER JOIN `course`  as co ON c.category_id = co.category_id
  INNER JOIN `courseandprof`   as cp ON  co.course_id = cp.course_id
  INNER JOIN `professor`    as p ON  p.prof_id = cp.professor_id
  WHERE  c.category_id = 1

You set category_id into this request

您将category_id设置为此请求

#1