Re: Re: LEFT JOIN query help

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.�p��Ң > �s;KK�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_answered5��,�7�3��� > �m�.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�)�#&Kߠ�W >> �].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 >