JOIN 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOIN 3 tables

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 18:24:08

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 23:32:34

it is possible

SELECT *
FROM Orders o
INNER JOIN OrderItems oi
ON oi.orderID =o.orderID 
INNER JOIN Products p
ON p.productID =oi.productID 
WHERE o.purchesDate BETWEEN @d1 AND @d2
AND (p.maincategory =@category
OR p.subCategory=@category)

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-03 : 07:46:35

thanks visakh16

but
I need to SUM(oi.price) after all the JOINs and the WHEREs

where do i put it?

i want to sum the price column in the OrderItems table

after filtering prices of products that where not purchased between to 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

and why do i have to selecet the clientID and the totalPrice columns of the Orders table?

I think it should start
SELECT SUM(price) FROM OrderItems....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:12:00

is this what you want?

SELECT SUM(oi.price) AS TotalPrice
FROM Orders o
INNER JOIN OrderItems oi
ON oi.orderID =o.orderID 
INNER JOIN Products p
ON p.productID =oi.productID 
WHERE o.purchesDate BETWEEN @d1 AND @d2
AND (p.maincategory =@category
OR p.subCategory=@category)

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-03 : 12:01:46

wonderfull!!
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:04:46

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources