sql - 为什么我在查询中得到重复的结果？

Question

我正在使用 Moodle 数据库查询学生和老师为他们所注册的相关类(class)发布的最新论坛帖子。

以下是我需要加入的表以及它们包含的内容的解释:

mdl_forum_posts = "all posts are stored in this table"
mdl_forum_discussions = "forums are composed as discussions"
mdl_user = "information stored about users"
mdl_log = "activity of every user of the system be it, log in - adding a post"
mdl_user_enrolments = "users participating in courses"
mdl_enrol = "instances of enrolment plugins used in mdl_course"
mdl_course = "courses available in the system"

这是我的查询:

SELECT l.time AS TimeofPost, l.action as Action, usr.id as UserID, 
       usr.firstname,usr.lastname, c.id as CourseID , c.fullname, 
       c.idnumber, fd.name, fd.timemodified as CreatedOn, fp.created, 
       fp.modified, fp.subject, fp.message

FROM mdl_forum_posts fp

LEFT JOIN mdl_forum_discussions fd ON fp.discussion = fd.id
LEFT JOIN mdl_user usr ON fp.userid = usr.id
LEFT JOIN mdl_log l ON usr.id = l.userid
LEFT JOIN mdl_user_enrolments ue ON usr.id = ue.userid
LEFT JOIN mdl_enrol e ON ue.enrolid = e.id
LEFT JOIN mdl_course c ON e.courseid = c.id

WHERE (action = 'add post' OR action = 'add discussion')    

我遇到的问题是我得到了重复的结果。

Answer 1

您当前正在将 mdl_forum_posts 链接到 userid 上的 mdl_log。

这意味着特定用户的每个记录的操作记录都将链接到该用户发布的每个帖子(而不是将特定帖子链接到发布操作的日志)。