Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: open

Message Too Old, No Replies

can't get two queries to work together

mysql, join

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

New User

5+ Year Member

joined:Feb 9, 2010
posts: 26
votes: 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.
7:16 pm on Feb 9, 2010 (gmt 0)

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


You need to put the "LIMIT 1" outside the parenthesis where it is now.
7:35 pm on Feb 9, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 9, 2010
posts: 26
votes: 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
7:44 pm on Feb 9, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 9, 2010
posts:26
votes: 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')
7:53 pm on Feb 9, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 9, 2010
posts:26
votes: 0


Correction, I had to add:
AND
orders.order_id = order_details.order_id
5:09 pm on Feb 10, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 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')
)