You cant have subqueries (select from where) in the where clause. On Wed, 2003-10-08 at 15:00, Mark Wilson wrote: > BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL > doesn't like the single quotes around the table name, so I took them out. > Now THIS query works: > SELECT MAX( plan_submission_number ) > FROM artifacts > WHERE product_id = '1' > -- (returns '2') > > But this one still doesn't: > SELECT * from artifacts > WHERE (product_id = '1' AND > plan_submission_number = (SELECT MAX( plan_submission_number ) > FROM artifacts > WHERE product_id = '1') > ) > > Although that seems as if it should be equivalent to the following > hard-coded version: > SELECT * > FROM `artifacts` > WHERE (product_id = '1' AND plan_submission_number = '2') > > -- > Mark Wilson, Computer Programming Unlimited (cpuworks.com) > Web : http://cpuworks.com Tel: 410-549-6006 > Email: [EMAIL PROTECTED] Fax: 410-549-4408 > > > Quoting Mark Wilson <[EMAIL PROTECTED]>: > > > I have an app for which people can submit plans. > > Each plan relates to a particular product. > > A new plan can be submitted for the same product, so each plan has its own > > submission number. (1,2,3...) > > Each plan is composed of artifacts. > > The (artifacts) table looks like this: > > artifact_id INT > > product_id INT > > plan_submission_number INT > > (etc) > > > > Task: get all the items for the most recent (i.e., highest) submission plan > > for > > a particular product. > > > > Since I'm relatively new to MySQL, and haven't mastered much beyond the most > > basic SELECTs, much less JOINs, I'm not sure how to do this. I think the > > following should work (for product_id = 1), but it returns a syntax error. > > > > SELECT * > > FROM `artifacts` > > WHERE ( product_id = '1' AND plan_submission_number = ( > > SELECT MAX( plan_submission_number ) > > FROM 'artifacts' > > WHERE product_id = '1' ) ) > > --------------- > > Error message: > > You have an error in your SQL syntax. Check the manual that corresponds to > > your > > MySQL server version for the right syntax to use near 'SELECT MAX( > > plan_submission_number ) > > FROM 'artifacts' > > WHERE p > > --------------- > > What am I missing? Thanks.... > > > > - Mark > > > > -- > > Mark Wilson, Computer Programming Unlimited (cpuworks.com) > > Web : http://cpuworks.com Tel: 410-549-6006 > > Email: [EMAIL PROTECTED] Fax: 410-549-4408 > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- **************************************************************** * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builder http://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * ****************************************************************
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
