Problem with Select

[Rossi]

Hi,

I have a problem with selecting the data I need from my db...

 

So, I have three tables, "wordlist" (id, term,...) , "users" (id_user, is_reviewer = 1/0,...) and "is_reviewed" (wordlist_id, id_user, is_reviewed = 1/0)

 

Every user can be a reviewer and every word in the "wordlist" needs to get reviewed by all reviewers. Therefore the "is_reviewed Table, which stores every reviewer who has (or hasn't) reviewed a word

 

So basically my question is, how can I select all the words/terms from wordlist that got reviewed by all Reviewers (if it's even possible in Mysql)

 

Hope you understand everything and can help me somehow...

[sunfighter]

Query the "users" table for is_reviewer = 1 and get the COUNT. This tells you how many reviewers you have.

 

Next Step through each id from "wordlist" table querying "is_reviewed" table WHERE wordlist_id = equals each "wordlist" id AND is_reviewed = 1.

 

Again get the COUNT. If both counts are the same That "wordlist" ID has been reviewed by all reviewers.

   

[Rossi]

Ok thanks for your help. So there isn't a way to do this just in MySQL, right? (= in only one query) just as I thought...

 

The reason I'm asking is, I'm changing another person's (PHP) source code and it would be easier if this would be possible in just one query... guess I have to do it the "hard" way

 

Anyways, thanks again

[DavidAM]

Something like this should be close:

 

SELECT wordlist.id, wordlist.term
FROM wordlist JOIN is_reviewed ON wordlist.id = is_reviewed.wordlist_id
WHERE is_reviewed.is_reviewed = 1
GROUP BY wordlist.id, wordlist.term
HAVING COUNT(is_reviewed.id_user) = (SELECT COUNT(id) FROM users WHERE is_reviewer = 1)

 

I have not tested this code -- I don't have a test environment here.

 

This should return the wordlist entries that have the same number of is_reviewed as there are reviewers.

[Rossi]

Wow, it works perfectly, you really helped me out a lot. Thank you!