homepage Welcome to WebmasterWorld Guest from 107.20.131.154
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
can't get two queries to work together
mysql, join
sukebe




msg:4077144
 7:09 pm on Feb 9, 2010 (gmt 0)

Hello, I'm trying to get 2009 sales totals on particular products, but I'm having trouble with this query. Both work as separate entities:

SELECT product_code, price FROM `order_details`
WHERE
product_code = 'CS007BLK'
AND
order_id = (
SELECT order_id FROM `orders`
WHERE
timestamp BETWEEN UNIX_TIMESTAMP('2009-01-01 01:00:02')
AND
UNIX_TIMESTAMP('2009-12-31 23:59:03' LIMIT 1)
)

I get :
Subquery returns more than 1 row

Please help this beginner.

 

LifeinAsia




msg:4077147
 7:16 pm on Feb 9, 2010 (gmt 0)

You need to put the "LIMIT 1" outside the parenthesis where it is now.

sukebe




msg:4077152
 7:35 pm on Feb 9, 2010 (gmt 0)

oops, well, that only gets one item, and only when I do
LIKE 'CS00%' IN THE MAIN QUERY.
SHOULD THIS BE A LEFT-JOIN type?

Thanks

sukebe




msg:4077160
 7:44 pm on Feb 9, 2010 (gmt 0)

I think I solved it with this:
SELECT order_details.product_code, order_details.price FROM order_details, orders
WHERE
order_details.product_code LIKE 'CS00%'
AND
orders.timestamp BETWEEN UNIX_TIMESTAMP('2009-01-01 01:37:02') AND UNIX_TIMESTAMP('2009-12-31 23:37:03')

sukebe




msg:4077164
 7:53 pm on Feb 9, 2010 (gmt 0)

Correction, I had to add:
AND
orders.order_id = order_details.order_id

syber




msg:4077751
 5:09 pm on Feb 10, 2010 (gmt 0)

No need to do a JOIN, your original try was close:

SELECT product_code, price
FROM `order_details`
WHERE product_code = 'CS007BLK' AND
order_id IN (
SELECT order_id FROM `orders`
WHERE
timestamp BETWEEN UNIX_TIMESTAMP('2009-01-01 01:00:02')
AND
UNIX_TIMESTAMP('2009-12-31 23:59:03')
)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved