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.