Question join 3 tables in one select query

[elic05]

I have 3 tables

1. Orders orderID (PK),clientID,purchesDate,totalPrice
2. OrdersItems orderID (FK),productID (FK),units,price
3. Products productID (PK),mainCategory,subCategory

I have to get the total sum of all products that where sold between two dates

I understand that...
first i have to select the orderIDs that where ordered between the 2 given dates
from the Orders table (cause only in that tabe the dates are written).
SELECT orderId FROM Orders WHERE purchesDate BETWEEN @d1 AND @d2

then I have to get all the productIdDs that the user choosed they are filtered by the user choosing (specific main-category or sub-category. threfore they have to be selected from the Products table.
SELECT productID FROM Products WHERE category = @category

then i have to get from the OrderItems table a list of all productIDs that their orderIDs inner join with orderIDs that I got in the first stage and also are inner joined with the productIDs the user choosed and to SUN the price column for those specific ProductIDs

is it possible to write such a query in one select?

 

[daniel02_0403]

select t1.*, t2.orderID, t3.productID
from OrdersItems t1
left join Orders t2 on t1.orderID = t2.orderID
left join Products t3 on t1.productID = t3.productID
where t1.purchesDate between @d1 and @d2 and
t3.category = @category

try the sql script above

just one question .. which category do you actually want to filter? mainCategory or subCategory?

 

[elic05]

thanks but where is the SUM function?

i want to sum the price column in the OrderItems table

after filtering prices of products that where not purchased between two given dates and filtering prices of products that are not of the given main category and given subcategory

in other words
I want to know how much money i got by selling products of sepecific category between two dates

 

[daniel02_0403]

oops .. sorry .. my bad

but I think there's something lacking in your orderitems table ...

if you want to retrieve how much you earned ...
you need a field that should have the value of how many of that specific product was sold ....

the one you posted before just gives us the information how much a single product cost ....
not how many of that product was sold ..

what i mean to say is that

(number of products sold) * (the cost of the product) = totalcost

and then we will have to sum the totalcost in order for us to retrieve how much money we get by selling products of specific category between 2 dates ...

I would suggest that your orderitems table be like this

orderID (FK),productID (FK),units,price,no_of_products_sold


or is it that this "unit" is the number of products sold?



with my table suggestion the query would be like this

select sum(t1.no_of_products_sold * t1.price) as earnings
from OrdersItems t1
left join Orders t2 on t1.orderID = t2.orderID
left join Products t3 on t1.productID = t3.productID
where t1.purchesDate between @d1 and @d2 and
t3.category = @category


but if the "unit" attribute is the number of products sold then it would be this

select sum(t1.units * t1.price) as earnings
from OrdersItems t1
left join Orders t2 on t1.orderID = t2.orderID
left join Products t3 on t1.productID = t3.productID
where t1.purchesDate between @d1 and @d2 and
t3.category = @category


just tell me if this is not what you want ...
i will help you resolve your problem ...

hope this helps you

 

[cjard]

select t1.*, t2.orderID, t3.productID
from OrdersItems t1
left join Orders t2 on t1.orderID = t2.orderID
left join Products t3 on t1.productID = t3.productID
where t1.purchesDate between @d1 and @d2 and
t3.category = @category

There is no point performing a left outer join if youre then going to reference that table in the where clause because it necessarily thus becomes an INNER JOIN. Please also give tables sensible aliases, not t1, t2, t3 (for the same reason you dont ahve a UI full of Button1 Button2 Button3)

is it possible to write such a query in one select?

Yes

SELECT
  p.productID,
  SUM(price) as sumPrice

FROM 
  Orders o
  INNER JOIN
  OrderItems oi
  ON
    o.orderID = oi.orderID

  INNER JOIN
  Propducts p
  ON
    oi.productID = p.productID

WHERE 
  o.purchesDate BETWEEN @date1 AND @date2 AND
  p.category = @category

GROUP BY
  p.productID

That generates a list of all productIDs sold on orders made between the two dates, with category chosen by the user, and the sum of the price of every productid


So suppose if in your date range and chosen category, you had 2 distinct products that sold. ID 1 sold once and costs 100, ID 2 sold three times and costs 200. You'd have:

productID, sumPrice
1, 100
2, 600

...

this means productID 2 would appear 3 times at 200 each, it would be grouped because of the GROUP BY and summed so the sum is 3 * 200 = 600


;
If you want to know how much money you made overall, and are not interested in drilling down into which product ids, simply remove the GROUP BY, and also the productID in the select list, leaving just the sum()