Getting rows based on another table...

[techiefreak05]

Sorry if the title seems weird, but Im tired.. haha

 

I have this query...

 

<?php
$getQs=mysql_query("SELECT * FROM RQanswered,RQquestions WHERE RQquestions.ID != RQanswered.qID ORDER BY RAND() LIMIT 1",$conn) or die(mysql_error());
while($r=mysql_fetch_assoc($getQs)){
$qID=$r["ID"];
$qText=$r["question"];

echo "<input type='hidden' name='q_ID' value='" . $qID . "' />";
echo "<center>Your random question is...<br /><h1>" . $qText . "</h1></center><br />";
echo '<center><textarea name="msgBody" rows="10" cols="54"></textarea></center><br />';
echo "<center><input type='submit' name='doRQ' value='Submit' /></center>";

}
?>

 

I just basically need a query that grabs the "ID" from "RQquestions" and matches those IDs against the "qID" in another table called "RQanswered" and makes sure they dont match

 

Just grab the RQquestion data WHERE the ID doesnt already exist in qID under RQanswered..

 

again im sorry if this doesnt make sense, but im really tired haha

 

thanks!

 

Its a questions database that spits a random question to a user, and i dont want them to get shown a question theyve already answered...

 

--

 

EDIT: I forgot to say that... it does get the info, it just gets the questions that HAVE already been answered, but thats not wanted..

[fenway]

Sounds like you want a left join... but why the limit?

[techiefreak05]

Well I have a table of questions (RQquestions).. and another table that hold the questions that each user has answered (RQanswered)

 

I just need a query that will grab a random question out of the questions table, that a user has not answered already.

 

The limit is because I just need one random question

[fenway]

Then you want:

 

SELECT a.* FROM RQquestions AS q LEFT JOIN RQanswered AS a ON ( q.ID = a.qID ) WHERE a.qID IS NULL ORDER BY RAND() LIMIT 1

[techiefreak05]

That query didnt quite work.. so I modified it and got this

 

SELECT a . *
FROM RQquestions AS q
LEFT JOIN RQanswered AS a ON ( q.ID = a.qID )
WHERE a.userID = '1180540351'
ORDER BY RAND( ) 

 

that query gets all the questions i HAVE answered, now how do i just reverse it?

[Hooker]

You're missing a vital peice of fenway's example.

 

When you do a left join it doesn't just select matching rows, it selects everything from the left table, if the field you choose to join the tables on don't match it will simply add a NULL in the field of the right table (the table being joined) so:

 

SELECT a . *
FROM RQquestions AS q
LEFT JOIN RQanswered AS a ON ( q.ID = a.qID )
WHERE a.userID = '1180540351'
AND WHERE a.qID IS NULL
ORDER BY RAND( )

 

Should do it.

[techiefreak05]

that gave an error so i did this

 

SELECT q.*
FROM RQquestions AS q
LEFT JOIN RQanswered AS a ON ( q.ID = a.qID )
WHERE a.userID = '1180540351'
AND a.qID IS NULL
ORDER BY RAND( )

 

but it returned 0 rows...

 

I need to select q.* because I have to grab the question info

[fenway]

You need to use this:

 

SELECT q.*
FROM RQquestions AS q
LEFT JOIN RQanswered AS a ON ( q.ID = a.qID AND a.userID = '1180540351' )
WHERE a.qID IS NULL
ORDER BY RAND( )

Since a.userID always will be null in this LEFT JOIN.