wierd (IMO) query .. help me build please

[severndigital]

i need to get the SUM of a field from one table based off a range in another table

 

here's what i think it should should like in the end, but of course it isn't working

 

SELECT SUM(prd.qty) FROM order_products prd
WHERE 
order_list.datemade BETWEEN UNIX_TIMESTAMP('2008-10-01') AND UNIX_TIMESTAMP('2008-10-31')

 

i should mention that the tables look like this

order_list table

id

datemade

etc....

 

order_prods table

id

orderid

prodid

qty

etc...

 

 

 

 

 

i just can't get this query to show up. Any suggestions??

 

Thanks

-C

 

 

 

 

[fenway]

"Show up"?  I assume the query doesn't fail? How are you requesting this column?

[rhodesa]

you need a join there...try this:

 

SELECT SUM(prd.qty) FROM order_products prd
   LEFT JOIN order_list lst ON lst.id = prd.orderid
   WHERE lst.datemade BETWEEN UNIX_TIMESTAMP('2008-10-01') AND UNIX_TIMESTAMP('2008-10-31')