CODEWITHSUNDEEP
sqlsql-server
Hassan_Jaffrani
Hassan_Jaffrani

Reputation: 55

join three tables sql

I have 3 tables 

sales
-----------
date 
order_id

product_details 
-----------
product_id
cost
price

order_detail 
-----------
product_id
order_id

I have to minus the sum of cost and price where date is between __ and __ to show the profit or loss

I have tried this but dont what is the result is showing

SELECT ( SUM(p.price) - SUM(p.cost) )
FROM   product_details AS p
       LEFT JOIN order_detail AS o
         ON o.product_id = p.product_id
       JOIN sales AS s
         ON s.order_id = o.order_id
WHERE  s.[date] = ' 15.08.2013' 

Upvotes: 0

Views: 107

Answers (3)

Sonam
Sonam

Reputation: 3466

Your query is correct. Just confirm the date range your are using. Or try getting the Dates in a query and then use it accordingly in the where clause. 

SELECT s.[date],( SUM(p.price) - SUM(p.cost) )
FROM   product_details AS p
LEFT JOIN order_detail AS o
ON o.product_id = p.product_id
JOIN sales AS s
ON s.order_id = o.order_id
group by s.[date]

As the Order Id is not unique then please try this:

with order_cte(product_id,order_id)as 
(select distinct Product_id, Order_Id from order_detail)

SELECT s.[date],( SUM(p.price) - SUM(p.cost) )
FROM   product_details AS p
LEFT JOIN order_cte AS o
ON o.product_id = p.product_id
JOIN sales AS s
ON s.order_id = o.order_id
group by s.[date]

Upvotes: 1

Question3CPO
Question3CPO

Reputation: 1202

Sounds like you need a GROUP BY in your clause, as you're looking for the total profit of each product id?

SELECT SUM(pd.price) - SUM(pd.cost)
FROM product_details pd
    LEFT JOIN order_details od ON pd.product_id = od.product_id
    INNER JOIN sales s ON od.order_id = s.order_id
-- This is where you'd change your date filter
WHERE s.Date BETWEEN '20.07.2013' AND '20.08.2013'
GROUP BY pd.product_id

Upvotes: 1

Doug Morrow
Doug Morrow

Reputation: 1366

Assuming product_id and order_id are unique:

SELECT SUM(p.price) - SUM(p.cost) AS Profit
FROM sales s
INNER JOIN order_detail AS o ON s.order_id = o.order_id
INNER JOIN product_details AS p ON o.product_id = p.product_id
WHERE s.Date BETWEEN '15.08.2013' AND '16.08.2013'

Upvotes: 1

Related Questions