[SOLVED] Help with a Query

[JSHINER]

SELECT 
quizzes.*, quizzes_categories.*, quizzes_taken.* 
FROM 
quizzes, quizzes_categories, quizzes_taken 
WHERE 
quizzes.category = quizzes_categories.id_cat AND quizzes.id = quizzes_taken.id_quiz AND id_user != '00123466' 
ORDER BY RAND() LIMIT 5

 

The quizzes_taken table looks like this:

 

id  |  id_quiz  |  id_user

-----------------------------

1    |      13    | 00123466

 

And I want to make it so that if there is an entry for that user (id_user) with the id_quiz (matches to quizzes.id) it does not return it.

 

Right now that query returns 0 results.

[zq29]

I think I understand what you want to achieve, give this a try...

SELECT * FROM `quizzes` as q 
LEFT JOIN `quizzes_categories` as qc ON q.`category` = qc.`id_cat` 
LEFT JOIN `quizzes_taken` as qt ON q.`id` = qt.`id_quiz` 
WHERE qt.`id_user` IS NULL 
ORDER BY RAND() LIMIT 5

[JSHINER]

Ok that works and returns results however it returns too many.

 

The quizzes_taken has one entry, and that quiz should not show up in the results.

 

How can I get that one out of there.

 

[craygo]

Semi's example will give back all users. just add the user you are looking for.

 

This user should be in a main table that contains the users not just a table that has links to it.

 

Ray

 

[zq29]

Semi's example will give back all users. just add the user you are looking for.

 

This user should be in a main table that contains the users not just a table that has links to it.

 

Ray

Oh yes, missed out the bit to narrow it down to a specific user...

SELECT * FROM `quizzes` as q 
LEFT JOIN `quizzes_categories` as qc ON q.`category` = qc.`id_cat` 
LEFT JOIN `quizzes_taken` as qt ON q.`id` = qt.`id_quiz` AND qt.`id_user`='00123466' 
WHERE qt.`id` IS NULL 
ORDER BY RAND() LIMIT 5

[JSHINER]

I don't want it to return results from quizzes if there's an entry in quizzes_taken for the id_user and id_quiz

 

So for example if in quizzes_taken there's an entry for quiz 1 taken by user 00123466 - quiz 1 will not return in the results.

 

Does that make sense?

[craygo]

try this instead

SELECT * FROM `quizzes` as q 
LEFT JOIN `quizzes_categories` as qc ON q.`category` = qc.`id_cat` 
LEFT JOIN `quizzes_taken` as qt ON q.`id` != qt.`id_quiz` AND qt.`id_user`='00123466' 
WHERE qt.`id` IS NULL 
ORDER BY RAND() LIMIT 5

 

ray