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... 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. 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 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. 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! Link to comment https://forums.phpfreaks.com/topic/240118-problem-with-select/#findComment-1234202 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.