Welcome to WebmasterWorld Guest from 54.160.163.163

Forum Moderators: open

Message Too Old, No Replies

can't get two queries to work together

mysql, join

     

sukebe

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

5+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

sukebe

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

5+ Year Member



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

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

5+ Year Member



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

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

5+ Year Member



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

syber

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

10+ Year Member



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')
)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month