var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: JOIN problem
On a shared document system, I have a table which has three fields: pageid, editid, and userid. Each user may edit any page zero or more times.
I want to find all pages in which user 1 and user 2 have both edited, and get a list of all the revisions they made on these pages. The output should be sorted by page ID and look like this:
pageid editid userid
12 143 1
12 155 2
12 189 2
17 100 1
17 104 2
17 105 1
17 199 1
I tried this query but it is very slow, and it doesn't actually find the "mututal" pages:
SELECT editid FROM revision WHERE pageid IN (SELECT DISTINCT pageid FROM revision WHERE userid IN (1,2) ORDER BY pageid);
What is an efficient query?
Last edited by merik; 05-21-11 at 18:18.
Reason: slow query
I am not sure whether this is going to give the correct answer. The WHERE userid IN (1,2) is basically where userid = 1 OR userid = 2.
SELECT rev_id FROM revision WHERE pageid IN (SELECT pageid FROM revision WHERE userid IN (1,2) GROUP BY pageid HAVING COUNT(*) = 2);
Thank you for your reply. Your query doesn't list the common pages either.
Never mind, I found the solution myself.