LEFT JOIN query help

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_tri via_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_tri via_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

Re: LEFT JOIN query help

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_tri via_knowledge_profile_reference`


WHERE

`verse_of_the_day_Bible_trivia`.`live` =1 AND
`my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_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_tri via_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_tri via_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

Re: Re: LEFT JOIN query help

Change 'NOT LIKE' to 'NOT IN' in the outer query.

Sent from my iPhone

On 19/07/2010, at 4:15 AM, "Ron Piggott" wr=
ote:

>=20
> 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.
>=20
> But mySQL is giving me the error that the subquery has more than 1 row ---=

> I have answered 2 questions.
>=20
> Would someone be able to clean up this query / sub query combination?
>=20
>=20
>=20
> SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM
> `verse_of_the_day_Bible_trivia`
>=20
> WHERE
>=20
> `verse_of_the_day_Bible_trivia`.`reference` NOT LIKE
>=20
> (
>=20
> SELECT `verse_of_the_day_Bible_trivia`.`reference`
>=20
> 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_tri=
via_reference`
> =3D `verse_of_the_day_Bible_trivia`.`reference`
> LEFT JOIN `my_Bible_trivia_knowledge_profile` ON
> `my_Bible_trivia_knowledge_profile`.`reference` =3D
> `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_knowledge_=
profile_reference`
>=20
>=20
> WHERE
>=20
> `verse_of_the_day_Bible_trivia`.`live` =3D1 AND
> `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_knowledge_=
profile_reference`
> =3D $user_reference
>=20
> )
>=20
> AND `verse_of_the_day_Bible_trivia`.`live` =3D1
>=20
> ORDER BY RAND() LIMIT 1
>=20
>>=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`
>>=20
>> 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 ar=
e
>> being eliminated with the WHERE clause.
>>=20
>> 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
>>=20
>> SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM
>>=20
>> ( `my_Bible_trivia_knowledge_profile` LEFT JOIN
>> `my_Bible_trivia_knowledge_questions_answered` ON
>> `my_Bible_trivia_knowledge_profile`.`reference` =3D
>> `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_knowledge=
_profile_reference`
>> )
>>=20
>> LEFT JOIN
>>=20
>> `verse_of_the_day_Bible_trivia` ON
>> `verse_of_the_day_Bible_trivia`.`reference` =3D
>> `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the _day_Bible_tr=
ivia_reference`
>>=20
>> WHERE
>>=20
>> `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_knowledge=
_profile_reference`
>> <> $user_reference AND
>> `my_Bible_trivia_knowledge_questions_answered`.`score` IS NULL AND
>> `verse_of_the_day_Bible_trivia`.`live` =3D1
>>=20
>> ORDER BY RAND() LIMIT 1
>>=20
>=20
>=20
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: LEFT JOIN query help

--=-DAk+kofZ7OUx26FqwQht
Content-Type: multipart/alternative; boundary="=-A09gO4qGeWwdaP3/646d"


--=-A09gO4qGeWwdaP3/646d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit


Thanks. That answer worked. Ron

--





-----Original Message-----
From: Kesavan Rengarajan
To: ron.piggott@actsministries.org
Cc: ron.piggott@actsministries.org ,
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" 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_tri via_knowledge_profile_reference`
>
>
> WHERE
>
> `verse_of_the_day_Bible_trivia`.`live` =1 AND
> `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_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_tri via_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_tri via_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
>

--=-A09gO4qGeWwdaP3/646d
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit










Thanks.  That answer worked.  Ron

Change 'NOT LIKE' to 'NOT IN' in the outer query.



Sent from my iPhone



On 19/07/2010, at 4:15 AM, "Ron Piggott" <> 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_tri via_knowledge_profile_reference`

> 

> 

> WHERE

> 

> `verse_of_the_day_Bible_trivia`.`live` =1 AND

>  `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_tri via_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_tri via_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_tri via_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 ()

> To unsubscribe, visit: 

>