Mysql – Adding where clause in LEFT OUTER JOIN giving unexpected result

join;MySQL

SELECT abc_discussions.id as id,
       abc_discussions.subject as subject,
       abc_todo_lists.title as todo_list_title, 
       abc_todos.title as todo_title,
       abc_files.title as file_title, 
       count(abc_comments.comment) as no_of_comments,
       MAX(abc_comments.posted) as last_comment_posted
FROM abc_discussions
LEFT OUTER JOIN abc_todo_lists
ON abc_discussions.commenttable_type = 'todo_list' AND abc_discussions.commenttable_id = abc_todo_lists.id 
LEFT OUTER JOIN abc_todos
ON abc_discussions.commenttable_type = 'todo_item' AND abc_discussions.commenttable_id = abc_todos.id 
LEFT OUTER JOIN abc_files
ON abc_discussions.commenttable_type = 'file' AND abc_discussions.commenttable_id = abc_files.id
LEFT OUTER JOIN abc_comments 
ON abc_discussions.id = abc_comments.discussion_id
GROUP BY abc_discussions.id

gives everything correctly, but if I will add WHERE abc_discussions.project_id = 2 it gives todo_list_titleandfile_titleas null, but if I will addWHERE abc_discussions.project_id = 1` it gives again correct result what may be the cause ?

abc_discussions table

CREATE TABLE $abc_discussions_table(
            id bigint(20) NOT NULL AUTO_INCREMENT,
            project_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL,
            subject VARCHAR (2000) NOT NULL,
            description VARCHAR (20000) NOT NULL,
            posted datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            commenttable_type VARCHAR (200),
            commenttable_id bigint(20),
            UNIQUE  KEY id(id)

Best Answer

That's because you violated the rule, that every column in the select clause has to be either in the group by clause or have an aggregate function applied to it.

When you don't follow this rule, a random for every group is chosen to display. The group by "collapses" the rows for each group and you have to specify which row to display with min(), max() or whatever aggregate function works for you.

MySQL is to my knowledge the only RDBMS that allows violating this rule, but you can turn this off by setting the sql mode only_full_group_by.

  • read more about it here
Related Question