Problem 1 : Your query contains a subquery : SELECT MAX( plan_submission_number ) > FROM 'artifacts' > WHERE product_id = '1' )
subqueries are (AFAIK) not supported in the latest production version of MySQL (4.0.15) but are coming soon...might already be in a beta ver Two possible solutions - 1) If there are a fixed number of artifacts per product (n): SELECT * FROM artifacts WHERE product_id = 1 ORDER BY plan_submission_number DESC LIMIT 1, n This retrieves the n most recent records for the specific product_id But obviously this only works if n is fixed... 2)Otherwise the only solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
