Solved

special join between 3 tables

Posted on 2007-12-05
5
205 Views
Last Modified: 2010-03-20
I need a query to find an totals for items and writeins for each company.  I have an Order table, an Orderdetails table, and a OrderWritein table.  Both OrderDetails and OrderWriteIns have a Price and Quantiy column.  I wrote this with joins as follows:
Select C.CompanyName,sum(D.price*D.Quantity) itmAmt,sum (W.price*W.Quantity) wiAmt,max(O.OrderDate) [Last Order]
From tblOrders O
Inner Join tblCompany C on O.companyid = C.companyid
Inner Join tblOrdersDetail D on O.orderid=D.orderid
Inner Join tblOrdersWriteIns W on O.orderid=W.orderid
group by C.companyName

The problem is when I join the write in table it does a cross join.  if I have 10 items and 10 writeIns then the sum is for the 100 rows.  How do I write the query so each company has the correct sum for both itemsDetail and writeIns?
0
Comment
Question by:moraleskp
  • 4
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20416613
Select C.CompanyName,
 (select sum(D.price*D.Quantity) from tblOrdersDetail D where O.orderid=D.orderid) itmAmt,
 (select sum (W.price*W.Quantity) from tblOrdersWriteIns W where O.orderid=W.orderid) wiAmt,
 max(O.OrderDate) [Last Order]
From tblOrders O
Inner Join tblCompany C on O.companyid = C.companyid
group by C.companyName
0
 
LVL 25

Accepted Solution

by:
imitchie earned 125 total points
ID: 20416624
this will work better..
Select OC.CompanyName,
 (select sum(D.price*D.Quantity) from tblOrdersDetail D where OC.orderid=D.orderid) itmAmt,
 (select sum (W.price*W.Quantity) from tblOrdersWriteIns W where OC.orderid=W.orderid) wiAmt,
 OC.[Last Order]
From (
 select C.CompanyName, max(O.OrderDate) [Last Order]
 FROM tblOrders O
 Inner Join tblCompany C on O.companyid = C.companyid
 group by C.companyName
) OC

Open in new window

0
 

Author Comment

by:moraleskp
ID: 20417054
imitchie,
Thanks for responding.
on the first response I got an error saying orderid was not in the group by clause.
In the second response got an error that orderId is an invalid column.  I guess because it is not selected in the sub query.
Your responses gave me some ideas so I tried the following code.  It seems to work.  



select companyName,sum(itmAmt),sum(wiAmt),max(Orderdate)
from
(Select O.orderid,C.CompanyName,
(select sum(D.PriceSold*D.qty) from tblOrdersDetail D where O.orderid=D.orderid)itmAmt,
(select sum(W.RetailPrice*W.Qty) from tblOrders_Writeins W where O.orderid=W.orderid) wiAmt,
O.orderdate
from tblOrders O
inner join tblCompany C on O.companyid=C.companyid
Group by C.companyName,O.orderid,O.orderdate
) query
group by companyName
Order by CompanyName

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20417073
You are right. Good on you to find a solution! This would probably have worked, and does less work

Select OC.CompanyName,
 (select sum(D.price*D.Quantity) from tblOrdersDetail D where OC.orderid=D.orderid) itmAmt,
 (select sum (W.price*W.Quantity) from tblOrdersWriteIns W where OC.orderid=W.orderid) wiAmt,
 OC.[Last Order]
From (
 select O.OrderID, C.CompanyName, max(O.OrderDate) [Last Order]
 FROM tblOrders O
 Inner Join tblCompany C on O.companyid = C.companyid
 group by C.companyName
) OC
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20417077
nvm, orderid is not in the group by..
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 25
MS SQL 2008 Divide by zero error encountered. Error 3 36
Access Crosstab Query with Multiple Values 4 32
awk and Pythagoras? 5 7
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question