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_titleand
file_titleas null, but if I will add
WHERE 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)
SHOW CREATE TABLE xarbo_discussions ;
JOIN
+GROUP BY
inflates aggregates likeCOUNT()
.LEFT
?