473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 4604
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Scott | last post by:
I have a view that contains the following SQL1 and it takes about 500ms to run with Explain Plan EXP1. However, if I add an order by clause(e.g. select * from view1 order by ID desc), it will then...
0
by: Pascal Délisle | last post by:
Hi! I try to figure out how to use a nested "SELECT" statement after the "IN" predicate. For example, when I try this code, it doesn't return anything although it should: SELECT...
1
by: Steve Thorpe | last post by:
Hi. I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL Server' to link one to the other and also vise versa. Each server has two users permissioned. My problem is...
3
by: Ian T | last post by:
Hi, I've got what I think (probably incorrectly) should be a simple SELECT : Two colums with data like col1 col2 1 50 1 51 2 50
5
by: Clifford W. Racz | last post by:
Has anyone solved the issue of translating lists in Word 2003 (WordML) into xHTML? I have been trying to get the nested table code for my XSLT to work for a while now, with no way to get the...
3
by: Sean Shanny | last post by:
To all, We are running postgresql 7.4.1 on an G5 with dual procs, OSX 10.3.3 server, 8GB mem, attached to a fully configured 3.5TB XRaid box via fibre channel. I think we have run into this...
1
by: imranpariyani | last post by:
Hi i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated.. the view is: CREATE OR REPLACE VIEW thsn.trade_view AS SELECT...
1
by: Max | last post by:
Hi! I have a problem with an XSL transformation. This is the XML file to transform: there's an "article" with one element of type "text". There are two nested elements of type "image" and "text":...
2
by: GISmatters | last post by:
I have unbound checkboxes in a nested gridview to allow multi-selection of "child" rows. For context, the parent gridview rows are for large "reports", the child rows are for various specific files...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.