Mysql 左连接查询不显示正确的值

Question

我有两个 mysql 表 person 和 courses_by_instructors。我正在执行 sql 查询，但没有得到所需的结果。我试图从 person 表中提取所有具有 instructor_roles = 1 的内容，然后我试图向正在教授类(class)的教师展示:SQLFIDDLE

人

+------------+-----------+----------------+-----------------+------------+
| company_id | person_id |  person_name   | instructor_role | staff_role |
+------------+-----------+----------------+-----------------+------------+
|          1 |       154 | James Newton   |               1 |          0 |
|          2 |       154 | James Newton   |               1 |          0 |
|          5 |       478 | Tina Turner    |               1 |          1 |
|          4 |       258 | James Fuller   |               1 |          0 |
|          5 |       714 | Michael Jordan |               1 |          0 |
|          6 |       714 | Michael Jordan |               1 |          0 |
|          7 |       964 | Steve Johnson  |               1 |          0 |
|          7 |       687 | Fred Willis    |               0 |          1 |
+------------+-----------+----------------+-----------------+------------+

讲师授课

+--------+------------+----------------+-----------+----------------+-----------+
| cbi_id | company_id |  course_name   | course_id |  person_name   | person_id |
+--------+------------+----------------+-----------+----------------+-----------+
|  1     |          1 | Business Logic |         1 | James Newton   |       154 |
|  2     |          5 | Emerging Tech  |        15 | Michael Jordan |       714 |
|  3     |          1 | Research       |        10 | James Newton   |       154 |
|  4     |          5 | Economics      |         9 | Tina Turner    |       478 |
+--------+------------+----------------+-----------+----------------+-----------+

1. 使用 instructor_role = 1 拉动所有人
2. 然后显示所有讲师，同时通过显示他们的 cbi_id 来表明他们是否在 courses_by_instructor 表中。并非所有这些都在该表中。

company_id = 5的当前结果

+-----------------+--------+
| instructor_name | cbi_id |
+-----------------+--------+
| James Newton    |   null |
| Tina Turner     |      4 |
| Michael Jordan  |      2 |
+-----------------+--------+

company_id = 5 的期望结果

+-----------------+--------+---------------+
| instructor_name | cbi_id | course_name   |
+-----------------+--------+---------------+
| James Newton    |        |               |
| James Newton    |        |               |
| Tina Turner     |      4 | Economics     |
| James Fuller    |        |               |
| Michael Jordan  |      2 | Emerging Tech |
| Michael Jordan  |        |               |
| Steve Johnson   |        |               |
| Fred Willis     |        |               |
+-----------------+--------+---------------+

SQL查询

SELECT p.person_id, p.person_name, p.instructor_role, IFNULL(lj.company_id, 0) as  company_id, lj.person_id, lj.cbi_id
FROM person as p
LEFT JOIN courses_by_instructors as lj
      ON (lj.person_id = p.person_id AND lj.company_id = 5)
WHERE instructor_role = 1
GROUP BY cbi_id
ORDER BY person_name

Answer 1

这部分很难理解你想要什么

 company_id=5

但我认为这应该可行......如果不让我知道需要改变什么:

SELECT 
p.person_name as "Instructor Name",
c.cbi_id,  as "C.B.I. I.D.",
c.course_name "Course Name"
FROM 
person as p
LEFT JOIN courses_by_instructors as c ON c.person_id = p.person_id
WHERE p.instructor_role = 1
GROUP BY p.person_id
ORDER BY p.person_name