List:General Discussion« Previous MessageNext Message »
From:Ron Piggott Date:August 31 2012 10:21am
Subject:Aggregate
View as plain text  
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 

Thread
AggregateRon Piggott31 Aug
  • RE: AggregateRick James4 Sep
    • RE: Aggregatehsv5 Sep
    • RE: Aggregatehsv23 Sep