Subqueries don't show up until v4.1 which I have running on one of my servers. Runs great and I have it into production.
--ja On Wed, 8 Oct 2003, Rory McKinley wrote: > 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]
