can't get two queries to work together

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)

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)

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

sukebe

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

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

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

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

syber

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