[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-db
Subject:    [PHP-DB] GROUP BY
From:       "Ron Piggott" <ron.piggott () actsministries ! org>
Date:       2011-05-11 3:30:26
Message-ID: 33ECF067503644B3877931276C02F5B0 () RonPiggottPC
[Download RAW message or body]


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  



[prev in list] [next in list] [prev in thread] [next in thread]