Thanks. That answer worked. Ron
|
--
|
From: Kesavan Rengarajan <k7@xxxxxxxx>
To: ron.piggott@xxxxxxxxxxxxxxxxxx <ron.piggott@xxxxxxxxxxxxxxxxxx>
Cc: ron.piggott@xxxxxxxxxxxxxxxxxx <ron.piggott@xxxxxxxxxxxxxxxxxx>, php-db@xxxxxxxxxxxxx <php-db@xxxxxxxxxxxxx>
Subject: Re: 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@xxxxxxxxxxxxxxxxxx> 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 >
