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