0
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)
6
  • Show us the output of SHOW CREATE TABLE xarbo_discussions ; Jul 1, 2016 at 9:06
  • @ypercubeᵀᴹ thank you for your kind reply , I work around that for now by removing the WHERE clause . still I am editing the question with what you have asked for, may be your idea will improve it. Jul 2, 2016 at 5:40
  • @ypercubeᵀᴹ please share if you have a better idea on this problem. that will be great help. Jul 2, 2016 at 5:43
  • 1
    JOIN + GROUP BY inflates aggregates like COUNT().
    – Rick James
    Jul 9, 2016 at 19:05
  • 1
    Do you really need LEFT?
    – Rick James
    Jul 9, 2016 at 19:06

1 Answer 1

2

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
1
  • how I solved it is I removed the where clause and retrieved all data after that removed unnecessary data with a foreach. Jul 2, 2016 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.