mysql - 修改 JOIN 以通过外部表中的 ID 列表过滤结果

Question

这是我的数据库结构:

我使用的 SQL 语句是:

SELECT
     t1.Name AS Teacher_Name,
     t2.Name AS Observer_Name,
     o.Datetime AS Datetime,
     o.Type AS Type,
     o.Year_Group AS Year_Group,
     o.Class_Name AS Class_Name,
     c.Title AS Course_Name,
     GROUP_CONCAT(DISTINCT l.Title SEPARATOR ', ') AS Focus,
     o.Achievement_Grade AS Achievement_Grade,
     o.Behaviour_Grade AS Behaviour_Grade,
     o.Teaching_Grade AS Teaching_Grade,
     GROUP_CONCAT(DISTINCT cl1.Title SEPARATOR ', ') AS Positive,
     GROUP_CONCAT(DISTINCT cl2.title SEPARATOR ', ') AS Development,
     o.Notes AS Notes
FROM observations o
LEFT JOIN teachers t1 
    ON o.Teacher_ID = t1.Teacher_ID
LEFT JOIN teachers t2 
    ON o.Observer_ID = t2.Teacher_ID
LEFT JOIN courses c 
    ON o.Course_ID = c.Course_ID
LEFT JOIN  foci f
    ON o.ID = f.Observation_ID
LEFT JOIN focus_labels l
    on f.focus_id = l.id
LEFT JOIN  criteria c1
    ON o.ID = c1.Observation_ID
LEFT JOIN criteria_labels cl1
    on c1.Criteria_ID = cl1.ID AND c1.Type = 'P'
LEFT JOIN  criteria c2
    ON o.ID = c2.Observation_ID AND c2.Type = 'D'
LEFT JOIN criteria_labels cl2
    on c2.Criteria_ID = cl2.ID
GROUP BY o.id
ORDER BY Datetime DESC

从这样的结果集返回的数据是:

---

我现在想要实现的是按类(class)进行搜索。

我的输入是一个 ID 列表，所以我希望我可以使用类似的东西:

SELECT * FROM Courses WHERE Course_ID IN (1, 2, 3, 4)。

显然这并不是那么简单，因为我的 Courses 在 JOIN 上:

LEFT JOIN courses c ON o.Course_ID = c.Course_ID。

我将如何修改此声明以包含此类搜索功能？

提前致谢

Answer 1

为什么不呢？只需在您的查询中添加 where 语句:

.............................
LEFT JOIN criteria_labels cl2
    on c2.Criteria_ID = cl2.ID
WHERE o.Course_ID IN (1, 2, 3, 4)
GROUP BY o.id
ORDER BY Datetime DESC