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:

CREATE TABLE events (
id int unsigned not null auto_increment,
description tinytext not null,
scheduled datetime not null,
PRIMARY KEY (id)
);

and

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’

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.

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

Typographical error!

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

okay, regarding the cross join effects, i can demonstrate it like this

first let’s create some sample data


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 –

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:

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…


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 –


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

:slight_smile:

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.

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.