LEFT JOIN query help
in [Databases]
|
From: "Ron Piggott" on 18 Jul 2010 13:20 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
From: "Ron Piggott" on 18 Jul 2010 14:15 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 >
From: Kesavan Rengarajan on 18 Jul 2010 18:21 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(a)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 >
From: Ron Piggott on 19 Jul 2010 01:51 Thanks. That answer worked. Ron -- -----Original Message----- From: Kesavan Rengarajan <k7(a)trk7.com> To: ron.piggott(a)actsministries.org <ron.piggott(a)actsministries.org> Cc: ron.piggott(a)actsministries.org <ron.piggott(a)actsministries.org>, php-db(a)lists.php.net <php-db(a)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(a)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 >
|
Pages: 1 Prev: NULL to 0 Next: always the logic that messes me up.... Database fun! |