GROUP BY

------=_NextPart_000_0028_01CC0F6A.3DFD4420
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


Is there a way in the query below that the â€=9CLEFT OUTER =
JOINâ€=9D 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_refer ence` ) =
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

===3D

SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_refer ence` =
, `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`.`filenam e`

FROM ( `verse_of_the_day_Bible_trivia` INNER JOIN =
`Bible_trivia_category` ON `Bible_trivia_category`.`reference` =3D =
`verse_of_the_day_Bible_trivia`.`Bible_trivia_category_refer ence` )

LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON =
`Bible_trivia_category`.`reference` =3D =
`verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_t rivia_categor=
y_reference`=20

WHERE `verse_of_the_day_Bible_trivia`.`live` =3D 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 >=3D10

ORDER BY `verse_of_the_day_Bible_trivia`.`reference` ASC

===3D


`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=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D35 ;


`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=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D410 ;


`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=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D15 ;


The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20

------=_NextPart_000_0028_01CC0F6A.3DFD4420--