472,783 Members | 945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,783 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 4582
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: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

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.