SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2003
    Location
    Melbourne, Australia
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining a table twice (error no 1137)

    I'm looking at building a site which allows music students to submit stageplans for their performances. I'd like to be able to list the different "events" at which they perform along with the total number of stageplans submitted for that event and the number of stageplans submitted by the currently logged in user.

    I'm planning to have the user's id and "group" membership stored in a session so I don't have incorporate references to those attributes in every query. Anyway, that's beyond what's necessary to solve my current problem. I'm hoping those more experienced than me might be able to suggest something. Apologies if I'm repeating a question; my searches through this forum revealed nothing similar.

    With the following table definitions:

    Code SQL:
    CREATE TABLE events (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    description tinytext NOT NULL,
    scheduled datetime NOT NULL,
    PRIMARY KEY (id)
    );
    and
    Code SQL:
    CREATE TABLE stageplans (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    author_id INT NOT NULL,
    event_id INT NOT NULL,
    PRIMARY KEY (id)
    );

    I find that attempting to COUNT the stageplans with the following query throws the error: ERROR 1137 (HY000): Can't reopen table: 'allplans'
    Code SQL:
    SELECT
        events.*,
        COUNT(allplans.id) AS allplans,
        COUNT(my_plans.id) AS myplans
    FROM
        events
    LEFT JOIN
        stageplans AS allplans ON (events.id = allplans.event_id)
    LEFT JOIN
        stageplans AS my_plans ON (events.id = my_plans.event_id) AND myplans.author_id = '2'
    GROUP BY events.id
    I'm at a loss to understand why. It seems to me that what I'm doing is not so different to that described in the first two user comments at http://dev.mysql.com/doc/refman/5.1/en/join.html. (Sorry, there are no direct links to the comments.)

    I'd be grateful for any suggestions.
    Last edited by auricle; Jan 17, 2010 at 06:36. Reason: typo
    Zealotry is contingent upon 100 posts and addiction 200?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    my_plans or myplans? you use both in the query

    by the way, you don't want to join twice, as you'll get cross join effects and inflated counts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2003
    Location
    Melbourne, Australia
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    my_plans or myplans?
    Typographical error!

    Quote Originally Posted by r937 View Post
    by the way, you don't want to join twice, as you'll get cross join effects and inflated counts
    I admit I don't understand how that might be since each join has different criteria (one extracts the total number of plans while the second extracts those with a specific condition).

    As I said, I don't think what I'm doing is so different from what is described in the MySQL manual comments. Would you have offered the same advice to them? Or would you advise against this whenever there is a need to extract more than one "subset" of data from a single table?

    Thanks
    Zealotry is contingent upon 100 posts and addiction 200?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, regarding the cross join effects, i can demonstrate it like this

    first let's create some sample data
    Code:
    CREATE TABLE events
    ( id INTEGER 
    , foo VARCHAR(99)
    );
    INSERT INTO events VALUES
     (140,'eenie') 
    ,(141,'meenie')
    ,(142,'minie')
    ,(143,'moe')
    ;
    CREATE TABLE stageplans
    ( id INTEGER 
    , event_id INTEGER
    , author_id INTEGER
    );
    INSERT INTO stageplans VALUES
     ( 31,140, 1 ) -- todd is 1
    ,( 32,142, 1 ) -- todd is 1
    ,( 33,143, 1 ) -- todd is 1
    ,( 34,141, 2 ) -- you are 2
    ,( 35,142, 2 ) -- you are 2
    ,( 36,140, 3 ) -- i am 3
    ,( 37,141, 3 ) -- i am 3
    ,( 38,142, 3 ) -- i am 3
    ,( 39,143, 3 ) -- i am 3
    ;
    now before we do the counts, let's just show the rows returned by the joins --
    Code:
    SELECT events.*
         , allplans.event_id AS all_event
         , allplans.author_id AS all_author
         , my_plans.event_id AS my_event
         , my_plans.author_id AS my_author
      FROM events
    LEFT OUTER 
      JOIN stageplans AS allplans 
        ON allplans.event_id = events.id
    LEFT OUTER
      JOIN stageplans AS my_plans 
        ON my_plans.event_id 
       AND my_plans.author_id = 2
    ORDER 
        BY events.id
         , allplans.event_id 
         , allplans.author_id
         , my_plans.event_id 
         , my_plans.author_id
          
    140  eenie  140  1  141  2
    140  eenie  140  1  142  2
    140  eenie  140  3  141  2
    140  eenie  140  3  142  2
    141  meenie 141  2  141  2
    141  meenie 141  2  142  2
    141  meenie 141  3  141  2
    141  meenie 141  3  142  2
    142  minie  142  1  141  2
    142  minie  142  1  142  2
    142  minie  142  2  141  2
    142  minie  142  2  142  2
    142  minie  142  3  141  2
    142  minie  142  3  142  2
    143  moe    143  1  141  2
    143  moe    143  1  142  2
    143  moe    143  3  141  2
    143  moe    143  3  142  2
    the above intermediate data helps you see why the counts come back the same:
    Code:
    SELECT events.*
         , COUNT(allplans.id) AS allplans
         , COUNT(my_plans.id) AS myplans
      FROM events
    LEFT OUTER 
      JOIN stageplans AS allplans 
        ON allplans.event_id = events.id
    LEFT OUTER
      JOIN stageplans AS my_plans 
        ON my_plans.event_id 
       AND my_plans.author_id = 2
    GROUP 
        BY events.id
    
    140  eenie   4  4
    141  meenie  4  4
    142  minie   6  6
    143  moe     4  4
    now, you can sometimes use DISTINCT to get around the inflated results...
    Code:
    SELECT events.*
         , COUNT(DISTINCT allplans.id) AS allplans
         , COUNT(DISTINCT my_plans.id) AS myplans
      FROM events
    LEFT OUTER 
      JOIN stageplans AS allplans 
        ON allplans.event_id = events.id
    LEFT OUTER
      JOIN stageplans AS my_plans 
        ON my_plans.event_id 
       AND my_plans.author_id = 2
    GROUP 
        BY events.id
    
    140  eenie   2  2
    141  meenie  2  2
    142  minie   3  2
    143  moe     2  2
    but in general this is inefficient and often incorrect (as in this case)

    in your particular scenario, the "myplans" are actually included within the "allplans" so you can use a single join and a conditional count --
    Code:
    SELECT events.*
         , COUNT(allplans.id) AS allplans
         , COUNT(CASE WHEN allplans.author_id = 2
                      THEN 'mine!' ELSE NULL END) AS myplans
      FROM events
    LEFT OUTER 
      JOIN stageplans AS allplans 
        ON allplans.event_id = events.id
    GROUP 
        BY events.id
    
    140  eenie   2  0
    141  meenie  2  1
    142  minie   3  1
    143  moe     2  0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2003
    Location
    Melbourne, Australia
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's marvellous. Since it's all done with a single join, that would prevent the error I was getting.

    But evidently you were not getting it. Had I been able to get past that one I suppose I would have noticed that the results were wrong and would then have sought advice about the query.

    It's the error that was bugging me as much as anything (pun not intended).

    Thanks Rudy.
    Zealotry is contingent upon 100 posts and addiction 200?

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2003
    Location
    Melbourne, Australia
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In case anyone strikes the same issue...

    I just wanted to play around with the composition of the query so I used temporary tables. That was the problem.
    Zealotry is contingent upon 100 posts and addiction 200?


Bookmarks

Posting Permissions

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