I figured out last night’s query .... and it’s a dozy.
The sub query
SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE
`verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY
`verse_of_the_day_Bible_trivia`.`date_added` DESC
puts the questions into descending order making the INNER JOIN link with the
most recently added trivia question in each category. This gives me the
desired results, I don't know if there is a way to stream line this or not.
SELECT Bible_trivia_category_reference , date_added , question_count ,
filename , created FROM (
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` ,
`verse_of_the_day_bible_trivia_ready_made_handouts`.`created`
FROM (
SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE
`verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY
`verse_of_the_day_Bible_trivia`.`date_added` DESC
) AS 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`
GROUP BY `Bible_trivia_category`.`reference`
HAVING question_count >= 10
ORDER BY `verse_of_the_day_Bible_trivia`.`date_added` DESC )
AS bible_trivia_handouts
WHERE date_added > created
ORDER BY Bible_trivia_category_reference ASC
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
