int(1) does not mean what you think. Probably you want TINYINT UNSIGNED. int(25), bigint(250) -- Eh? INT is always 4 bytes (32-bits); BIGINT is always 8 bytes.
Use UNSIGNED wherever appropriate. You have DATETIME and TIMESTAMP. Keep in mind that TIMEZONE is timezone-aware; DATETIME is not. The two tables have a lot of the same fields -- you may need to rethink the schema. Both tables have the same PRIMARY KEY, and it is AUTO_INCREMENT. This is almost certainly a mistake. Wouldn't one "article" have multiple "discussions"? Please restructure the tables before we try to discuss your Aggregate question. > -----Original Message----- > From: Ron Piggott [mailto:ron.pigg...@actsministries.org] > Sent: Friday, August 31, 2012 3:22 AM > To: mysql@lists.mysql.com > Subject: Aggregate > > > 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_prefer > ence_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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
