Is there a way in the query below that the “LEFT OUTER JOIN” connects with only the most recently added entry in `verse_of_the_day_Bible_trivia` for each category ( `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) based on the column `verse_of_the_day_Bible_trivia`.`date_added` ?
The purpose of this query is to compare the most recently added Bible trivia questions ( `verse_of_the_day_Bible_trivia`.`date_added` ) from each category ( `Bible_trivia_category`.`reference` ) with the last time the category handout was created ( `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` ). If there are new questions since the last time the handout was created ( `verse_of_the_day_Bible_trivia`.`date_added` ) OR the category now has 10 or more questions then the handout will be re-created (through a cron job) based on the results of this query. The HAVING condition is to eliminate categories with less than 10 questions. - See table structures below Thank you for your help. Ron === SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` , `verse_of_the_day_Bible_trivia`.`date_added` , COUNT( `verse_of_the_day_Bible_trivia`.`reference` ) AS question_count, `verse_of_the_day_bible_trivia_ready_made_handouts`.`filename` FROM ( `verse_of_the_day_Bible_trivia` INNER JOIN `Bible_trivia_category` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_trivia_category_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` = 1 AND `verse_of_the_day_Bible_trivia`.`date_added` > `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` GROUP BY `Bible_trivia_category`.`reference` HAVING question_count >=10 ORDER BY `verse_of_the_day_Bible_trivia`.`reference` ASC === `Bible_trivia_category` CREATE TABLE IF NOT EXISTS `Bible_trivia_category` ( `reference` int(3) NOT NULL AUTO_INCREMENT, `category` varchar(45) NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; `verse_of_the_day_Bible_trivia` CREATE TABLE IF NOT EXISTS `verse_of_the_day_Bible_trivia` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL DEFAULT '0', `trivia_question` varchar(300) NOT NULL, `trivia_answer_1` varchar(150) NOT NULL, `trivia_answer_2` varchar(150) NOT NULL, `trivia_answer_3` varchar(150) DEFAULT NULL, `trivia_answer_4` varchar(150) DEFAULT NULL, `answer` int(1) NOT NULL DEFAULT '0', `explanation` varchar(1000) DEFAULT NULL, `Bible_verse_reference` varchar(60) DEFAULT NULL, `seasonal_use` int(1) NOT NULL DEFAULT '0', `date_added` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `assigned_date` date NOT NULL DEFAULT '0000-00-00', `store_catalog_reference` int(3) NOT NULL DEFAULT '0', `teaching_devotional_messages_reference` int(3) NOT NULL DEFAULT '0', `live` int(1) NOT NULL DEFAULT '0', `user_hits` int(25) NOT NULL DEFAULT '0', `user_hits_answer` int(25) NOT NULL DEFAULT '0', PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=410 ; `verse_of_the_day_bible_trivia_ready_made_handouts` CREATE TABLE IF NOT EXISTS `verse_of_the_day_bible_trivia_ready_made_handouts` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL, `filename` varchar(100) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `live` int(1) NOT NULL, `views` int(25) NOT NULL, PRIMARY KEY (`reference`), UNIQUE KEY `verse_of_the_day_Bible_trivia_reference` (`Bible_trivia_category_reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ; The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
