simply baffled... help

[petroz]

Hi Guys,

 

I am getting a little lost trying to create this query. I have a daily report that I need to run that gathers data from three different tables... I can't really give you a starting point, cause I can't seem to get there myself...

 

But here is goal. I have three tables... `items`, `orders` and `order_items`.

 

`Items` contains columns for `price` and a `item id`

`Orders` contains columns for `order_id` and `date`

`Order_items` contains columns for `order_id`, `item_id` and `quantity`

 

I need to get the sum total of all the orders for a specific date.

 

I am totally lost with this query.. any help would be greatly appreciated.

 

[Miss_Rebelx]

SELECT SUM(items.price) FROM items
INNER JOIN order_items ON items.item_id = order_items.item_id
INNER JOIN orders ON order_items.order_id = orders.order_id
WHERE orders.date = (DATE)

 

Not sure if you're going to manually type in the date, or use PHP (for example) to pass it a variable containing the date you want, but unless I messed up on the INNER JOIN (UGH! I hate them!) this query should work for you.

[petroz]

Thanks Miss_Rebelx!

 

I actually overlooked some fields in one of the tables that made this a lil easier. Here is what I have thats working...

 

SELECT SUM(`order_styles`.`sp` * `order_styles`.`quantity`) AS `order_totals` FROM (`order_styles`, `orders`) WHERE `order_styles`.`order_id` = `orders`.`id` AND `orders`.`issue_date` = '2010-09-23'

 

I am gonna test your solution out now... but I've always had problems clearly understanding joins!