Rossi Posted June 22, 2011 Share Posted June 22, 2011 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... Quote Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/ Share on other sites More sharing options...
sunfighter Posted June 22, 2011 Share Posted June 22, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/#findComment-1233386 Share on other sites More sharing options...
Rossi Posted June 22, 2011 Author Share Posted June 22, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/#findComment-1233404 Share on other sites More sharing options...
DavidAM Posted June 22, 2011 Share Posted June 22, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/#findComment-1233424 Share on other sites More sharing options...
Rossi Posted June 24, 2011 Author Share Posted June 24, 2011 Wow, it works perfectly, you really helped me out a lot. Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/#findComment-1234202 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.