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
