Re: Re: LEFT JOIN query help

Thanks. That answer worked. Ron -- -----Original Message----- From: Kesavan Rengarajan <k7@trk7.com> To: ron.piggott@actsministries.org <ron.piggott@actsministries.org> Cc: ron.piggott@actsministries.org <ron.piggott@actsministries.org>, php-db@lists.php.net <php-db@lists.php.net> Subject: Re: [PHP-DB] Re: LEFT JOIN query help Date: Mon, 19 Jul 2010 08:21:00 +1000 Change 'NOT LIKE' to 'NOT IN' in the outer query. Sent from my iPhone On 19/07/2010, at 4:15 AM, "Ron Piggott" <ron.piggott@actsministries.org> wrote: > > 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 >