I am still working on this query and wondering if I should be taking a different approach --- to use a sub query to figure out which questions have been answered and then an "outter" query to not select one of them. But mySQL is giving me the error that the subquery has more than 1 row --- I have answered 2 questions. Would someone be able to clean up this query / sub query combination? SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` WHERE `verse_of_the_day_Bible_trivia`.`reference` NOT LIKE ( SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` = `verse_of_the_day_Bible_trivia`.`reference` LEFT JOIN `my_Bible_trivia_knowledge_profile` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` =1 AND `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` = $user_reference ) AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 > > I am writing a Bible trivia application. I am trying to write the mySQL > query that will select the next question reference number and the current > question is answered. The value I want to retrieve It is in the field: > `verse_of_the_day_Bible_trivia`.`reference` > > I don't think I have my LEFT JOIN's right. When I take away the "WHERE" > clause only the records the user has answered are selected. Then they are > being eliminated with the WHERE clause. > > I am hoping the results join the 3 tables together --- really wide --- > with the user profile on the left hand side and then the question is the > middle and if the user has answered it then this record on the right hand > side, otherwise the fields are NULL. Does this make sense? Ron > > SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM > > ( `my_Bible_trivia_knowledge_profile` LEFT JOIN > `my_Bible_trivia_knowledge_questions_answered` ON > `my_Bible_trivia_knowledge_profile`.`reference` = > `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` > ) > > LEFT JOIN > > `verse_of_the_day_Bible_trivia` ON > `verse_of_the_day_Bible_trivia`.`reference` = > `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` > > WHERE > > `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` > <> $user_reference AND > `my_Bible_trivia_knowledge_questions_answered`.`score` IS NULL AND > `verse_of_the_day_Bible_trivia`.`live` =1 > > ORDER BY RAND() LIMIT 1 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
