mrc_titles is a temp table? On Wed, Oct 15, 2008 at 11:59 PM, Jerry Schwartz <[EMAIL PROTECTED] > wrote:
> I tried to make a query that joins to subqueries: > > > > SELECT discontinued.b > > FROM > > (SELECT mrc_titles.title AS a > > FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title > > JOIN pub ON prod.pub_id = pub.pub_id > > WHERE pub.pub_code = "MRC" > > AND prod.prod_discont = 1) AS `discontinued` > > > > LEFT JOIN > > (SELECT mrc_titles.title AS b > > FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title > > JOIN pub ON prod.pub_id = pub.pub_id > > WHERE pub.pub_code = "MRC" > > AND prod.prod_discont = 0) AS `available` > > ON discontinued.a = available.b > > WHERE available.b IS NULL > > ; > > > > Basically I'm trying to find the `mrc_titles.title` records that only match > where `prod`.`prod_discont` = 1, excluding those that match > `prod`.`prod_discont` = 0. > > > > I think the query makes sense to a human, but I get > > > > ERROR 1137 (HY000): Can't reopen table: 'mrc_titles' > > > > from MySQL 4.1.22-standard. > > > > I didn't see anything about this limitation in the 4.x documentation > (although somehow it seems to ring a bell). What am I missing? > > > > Regards, > > > > Jerry Schwartz > > The Infoshop by Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > <http://www.the-infoshop.com> www.the-infoshop.com > > <http://www.giiexpress.com> www.giiexpress.com > > www.etudes-marche.com > > > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
