problem with nested select...

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi Newsgroup

Hipe you can help me, I have a problem with a nested select. I have two
tables; one with products and one with prices of these products:
 - id, name
- pro_id, price, time (pro_id relates to

Each product gets a new prices during time and I would like to join the
two tables, but only get the newest price. Following SELECT should do
it, but it fails and I cant' find the error:

SELECT * FROM prices cp, product pro WHERE AND
cp.time IN ( SELECT MAX(cp1.time) FROM prices cp1 WHERE
cp1.product_id=cp.product_id );

The error message is the following:

#1064 - 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(cp1.time)
FROM mr_campaign_prices cp1
WHERE cp1.

Thanx alot...

Re: problem with nested select...

ask wrote:
Quoted text here. Click to load it

That syntax looks all right to me.  My guess is that you're using MySQL
4.0 or earlier.  Subqueries are not implemented in MySQL until version 4.1.

Some queries involving subqueries can be rewritten without using a
subquery.  This type, where the subquery is performing an aggregate
function, is pretty tricky.  Try something like this:

   SELECT cp.*, pro.*
   FROM prices AS cp INNER JOIN product AS pro ON cp.product_id =
     LEFT OUTER JOIN prices AS cp1
       ON (cp.product_id = cp1.product_id AND cp.time < cp1.time)
   WHERE cp1.time IS NULL

You can also upgrade to MySQL 4.1 or later, and use your original query.

Bill K.

Re: problem with nested select...

That is one spooky SQL-statement  :D  I'll have a closer look.

You are quite right about the version, I'm running an pre 4.1 version.

Thank you very mutch

Site Timeline