Results 1 to 4 of 4

Thread: JOIN problem

  1. #1
    Join Date
    Apr 2011
    Posts
    38

    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 19:18. Reason: slow query

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2011
    Posts
    38
    Thank you for your reply. Your query doesn't list the common pages either.

  4. #4
    Join Date
    Apr 2011
    Posts
    38
    Never mind, I found the solution myself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •