[prev in list] [next in list] [prev in thread] [next in thread]
List: php-db
Subject: Re: [PHP-DB] Re: LEFT JOIN query help
From: Ron Piggott <ron.piggott () actsministries ! org>
Date: 2010-07-19 5:51:51
Message-ID: 1279518711.1928.1.camel () localhost ! localdomain
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thanks. That answer worked. Ron
--
-----Original Message-----
From: Kesavan Rengarajan <k7@trk7.com>
To: ron.piggott@actsministries.org <ron.piggott@actsministries.org>
Cc: ron.piggott@actsministries.org <ron.piggott@actsministries.org>,
php-db@lists.php.net <php-db@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@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
>
[Attachment #5 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.30.2">
</HEAD>
<BODY>
<BR>
Thanks. That answer worked. Ron<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
--
<DIV ALIGN=center></DIV><BR>
<BR>
<DIV ALIGN=center><A HREF="http://www.TheVerseOfTheDay.info"><IMG \
SRC="cid:1279518711.1928.0.camel@localhost.localdomain" ALIGN="bottom" \
BORDER="0"></A></DIV> <BR>
<BR>
</TD>
</TR>
</TABLE>
-----Original Message-----<BR>
<B>From</B>: Kesavan Rengarajan <<A \
HREF="mailto:Kesavan%20Rengarajan%20%3ck7@trk7.com%3e">k7@trk7.com</A>><BR> \
<B>To</B>: ron.piggott@actsministries.org <<A \
HREF="mailto:%22ron.piggott@actsministries.org%22%20%3cron.piggott@actsministries.org%3e">ron.piggott@actsministries.org</A>><BR>
<B>Cc</B>: ron.piggott@actsministries.org <<A \
HREF="mailto:%22ron.piggott@actsministries.org%22%20%3cron.piggott@actsministries.org%3e">ron.piggott@actsministries.org</A>>, \
php-db@lists.php.net <<A \
HREF="mailto:%22php-db@lists.php.net%22%20%3cphp-db@lists.php.net%3e">php-db@lists.php.net</A>><BR>
<B>Subject</B>: Re: [PHP-DB] Re: LEFT JOIN query help<BR>
<B>Date</B>: Mon, 19 Jul 2010 08:21:00 +1000<BR>
<BR>
<PRE>
Change 'NOT LIKE' to 'NOT IN' in the outer query.
Sent from my iPhone
On 19/07/2010, at 4:15 AM, "Ron Piggott" <<A \
HREF="mailto:ron.piggott@actsministries.org">ron.piggott@actsministries.org</A>> \
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 (<A \
HREF="http://www.php.net/">http://www.php.net/</A>) > To unsubscribe, visit: <A \
HREF="http://www.php.net/unsub.php">http://www.php.net/unsub.php</A> >
</PRE>
</BODY>
</HTML>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic