Re: [PHP-DB] Re: LEFT JOIN query help

Thanks.  That answer worked.  Ron

-- 





-----Original Message-----
From: Kesavan Rengarajan <[email protected]>
To: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>,
[email protected] <[email protected]>
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" <[email protected]> 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
>