GROUP BY

["Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>]

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