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?

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.

Try this:

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.