Re: LEFT JOIN query help
| From: | Ron Piggott | Date: | Sun, 18 Jul 2010 18:15:33 +0000 |
| Subject: | Re: LEFT JOIN query help | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-47268@lists.php.net to get a copy of this message | ||
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 .
> <> $user_reference AND
>
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. 3U
> Z=reference =
>
> my_Bible_trivia_knowledge_questions_answered.verse_of_the_day_Bible_trivia_reference
>
> WHERE
>
> hmy_Bible_trivia_knowledge_profile_referencemy_Bible_trivia_knowledge_questions_answered8OT6!B
> .score IS NULL AND
>
> verse_of_the_day_Bible_trivia.live
> =1
>
> ORDER BY RAND() LIMIT 1
>