470,588 Members | 2,141 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,588 developers. It's quick & easy.

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=pro.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_prices cp1
WHERE cp1.
Thanx alot...

Dec 9 '05 #1
2 4532
ask wrote:
SELECT * FROM prices cp, product pro WHERE cp.product_id=pro.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)


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.
Dec 9 '05 #2
ask
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

Dec 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Scott | last post: by
reply views Thread by Pascal Délisle | last post: by
1 post views Thread by Steve Thorpe | last post: by
3 posts views Thread by Ian T | last post: by
3 posts views Thread by Sean Shanny | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.