problem with nested select...

[ask]

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:
product
- id, name
price
- pro_id, price, time (pro_id relates to product.id)

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 cp.product_id=p ro.id 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_pri ces cp1
WHERE cp1.


Thanx alot...

[Bill Karwin]

Re: problem with nested select...

ask wrote:[color=blue]
> SELECT * FROM prices cp, product pro WHERE cp.product_id=p ro.id 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)[/color]

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 = pro.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.

Regards,
Bill K.

[ask]

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