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
( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
- Original Message -
From: Mark Wilson [EMAIL PROTECTED]
To: Mysql Mailing List [EMAIL PROTECTED]
Sent: Wednesday, October 08, 2003 9:35 AM
Subject: Easy (?) conditional SELECT
I have an app
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
Er, I _used_ a basic SQL tutorial, which specifically said that should work.
The problem seems to be a limitation of MySQL, not general SQL operation.
That being said, are there any clever one-query options (using JOINs, etc?) or
is this basically a 2-step process in MySQL?
--
Mark Wilson,
On Wed, 2003-10-08 at 15:06, Mark Wilson wrote:
Er, I _used_ a basic SQL tutorial, which specifically said that this should
work. The problem seems to be a limitation of MySQL, not general SQL operation.
That being said, are there any clever one-query options (using JOINs, etc?) or
is this
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 -
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 =
Message-
From: Mark Wilson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 08, 2003 6:35 AM
To: Mysql Mailing List
Subject: Easy (?) conditional SELECT
I have an app for which people can submit plans.
Each plan relates to a particular product.
A new plan can be submitted
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: