I am unsure of which Aggregate function to use with the query (below) Right now thread_date_reference and last_post_membership_reference produce NULL results. I expect the NULL result is because of the LEFT OUTER JOIN and there being no matching JOIN in the table articles_discussion , as well as the GROUP BY (limiting 1 record result per thread)
SELECT `articles`.`reference` , `articles`.`article_title` , IF( `articles`.`date_submitted` > `articles_discussion`.`date_submitted` , CONVERT_TZ(`articles`.`date_submitted`,'+00:00','$user_time_zone_preference_time_string') , CONVERT_TZ(`articles_discussion`.`date_submitted`,'+00:00','$user_time_zone_preference_time_string') ) AS thread_date_reference , IF( `articles`.`date_submitted` > `articles_discussion`.`date_submitted` , `articles`.`membership_reference` , `articles_discussion`.`membership_reference` ) AS last_post_membership_reference FROM `articles` LEFT OUTER JOIN `articles_discussion` ON `articles`.`reference` = `articles_discussion`.`articles_reference` WHERE `articles`.`approved` IN ( 1 , 2 , 3 ) GROUP BY `articles`.`reference` The purpose of this query is to show a summary of the discussion threads with date of the most recent post ( thread_date_reference ) and the member # of that post ( last_post_membership_reference ). I am wanting to make the threads index into 4 queries to allow for different ORDER BY options: - newest to oldest posts (based on the date field) - oldest to newest posts (based on the date field) - thread titles alphabetical - by alias alphabetically (This will need an INNER JOIN to the “forum profiles” table. The tables articles and articles_discussion only store the auto_increment value of the member account # ) The table structure for articles CREATE TABLE IF NOT EXISTS `articles` ( `reference` int(25) NOT NULL AUTO_INCREMENT, `membership_reference` int(25) NOT NULL, `languages_list_reference` int(10) NOT NULL, `article_title` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `article` longtext COLLATE utf8_unicode_ci NOT NULL, `article_summary` varchar(2500) COLLATE utf8_unicode_ci NOT NULL, `remember_new_posting` int(1) NOT NULL COMMENT '1- Yes & 0- No', `date_submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `membership_reference_points_awarded` int(10) NOT NULL, `approved` int(1) NOT NULL, `inappropriate_content_flagged_by_membership_reference` int(25) NOT NULL, `inappropriate_content_flag_reasons_reference` int(2) NOT NULL, `inappropriate_content_flag_comments` varchar(2500) COLLATE utf8_unicode_ci NOT NULL, `inappropriate_content_flagged_date_submitted` datetime NOT NULL, `inappropriate_content_flag_moderator_membership_reference` int(25) NOT NULL, `inappropriate_content_flag_moderator_comments` longtext COLLATE utf8_unicode_ci NOT NULL, `inappropriate_content_flag_moderator_review_date` datetime NOT NULL, `html_head_keyword_1` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `html_head_keyword_2` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `html_head_keyword_3` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `html_head_keyword_4` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `html_head_keyword_5` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `html_head_description` varchar(500) COLLATE utf8_unicode_ci NOT NULL, `views` bigint(250) NOT NULL, PRIMARY KEY (`reference`) The table structure for articles_discussion CREATE TABLE IF NOT EXISTS `articles_discussion` ( `reference` int(25) NOT NULL AUTO_INCREMENT, `articles_reference` int(25) NOT NULL, `membership_reference` int(25) NOT NULL, ` languages_list_reference` int(10) NOT NULL, `discussion` longtext COLLATE utf8_unicode_ci NOT NULL, `remember_discussion_posting` int(1) NOT NULL COMMENT '1- Yes & 0- No', `date_submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `membership_reference_points_awarded` int(10) NOT NULL, `approved` int(1) NOT NULL COMMENT '1- auto approval; 2- flag for review; 3- review approval; 4- review deemed inappropriate and posting offline', `inappropriate_content_flagged_by_membership_reference` int(25) NOT NULL, `inappropriate_content_flag_reasons_reference` int(2) NOT NULL, `inappropriate_content_flagged_comments` varchar(2500) COLLATE utf8_unicode_ci NOT NULL, `inappropriate_content_flagged_date_submitted` datetime NOT NULL, `inappropriate_content_flag_moderator_membership_reference` int(25) NOT NULL, `inappropriate_content_flag_moderator_comments` longtext COLLATE utf8_unicode_ci NOT NULL, `inappropriate_content_flag_moderator_review_date` datetime NOT NULL, `views` bigint(250) NOT NULL, PRIMARY KEY (`reference`) I appreciate any help you are able to give me. Ron Piggott www.TheVerseOfTheDay.info
