Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- problem with nested select...
December 9, 2005, 11:50 am
rate this thread
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 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
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
Re: problem with nested select...
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.
- » Changing Tables from MyISAM to InnoDB... Cautions?
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum