Why am I getting duplicate results in my query?

Question

I am using the Moodle database to query the latest forum posts made by students and teachers for the relevant course they are enrolled in.

Here are the tables I need to join and an explanation in what they hold:

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"

Here is my query:

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')

The problem I have is that I am getting duplicate results.

You should show some sample data as well...With this many `JOIN`s it's easy to get dupes. — JNK, Apr 03 '12 at 13:45

score 2 · Answer 1 · answered Apr 03 '12 at 13:47

You are currently linking mdl_forum_posts to mdl_log on userid.

That means that every logged action record for a specific user will be linked to every post made by that user (instead of linking a specific post to the log of the action of posting).

Matt Gibson · Accepted Answer · 2012-04-03T13:59:46.693

No need to use the logs table - the fact that a post has been added and has a timestamp is all you need. Also, you don't really want to use LEFT JOIN unless you expect null values, which should never be the case here. I've left in the link to the enrolment table so that if any students are unenrolled, they won't show up, but this isn't really necessary.

You are getting duplicates because there are multiple matches for each forum post e.g. enrol table has many instances for each course. This query makes sure you just get stuff where there is one.

    SELECT fp.id, 
           usr.id as UserID, 
           usr.firstname,
           usr.lastname, 
           c.id as CourseID, 
           c.fullname, 
           c.idnumber, 
           fd.name, 
           fd.timemodified as DiscussionCreatedOn, 
           fp.created AS TimeofPost, 
           fp.modified, 
           fp.subject, 
           fp.message
      FROM mdl_forum_posts fp
INNER JOIN mdl_forum_discussions fd ON fp.discussion = fd.id
INNER JOIN mdl_forum f ON f.id = fd.forum
INNER JOIN mdl_course c ON f.course = c.id 
INNER JOIN mdl_user usr ON fp.userid = usr.id
     WHERE EXISTS (SELECT 1 
                     FROM mdl_user_enrolments ue
               INNER JOIN mdl_enrol e ON ue.enrolid = e.id 
                    WHERE usr.id = ue.userid 
                      AND e.courseid = f.course)

score 0 · Answer 3 · answered Apr 03 '12 at 13:33

0

   SELECT DISTINCT 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

The "word" DISTINCT may help you

answered Apr 03 '12 at 13:33

Deblaton Jean-Philippe

11,188
3
49
66

Hi thanks for getting back to me so soon, unfortunately i tried this but i am still getting the same result :(. – Moodlebook Apr 03 '12 at 13:37

Why am I getting duplicate results in my query?

3 Answers3