title: Joining table issue with SQL Server 2008
body: <p>I am using the following query to obtain some sales figures. The problem is that it is returning the wrong data.</p>
  
  <p>I am joining together three tables <code>tbl_orders</code> <code>tbl_orderitems</code> <code>tbl_payment</code>. The <code>tbl_orders</code> table holds summary information, the <code>tbl_orderitems</code> holds the items ordered and the <code>tbl_payment</code> table holds payment information regarding the order. Multiple payments can be placed against each order.</p>
  
  <p>I am trying to get the sum of the items <code>sum(mon_orditems_pprice)</code>, and also the amount of items sold <code>count(uid_orderitems)</code>. </p>
  
  <p>When I run the following query against a specific order number, which I know has 1 order item. It returns a count of 2 and the sum of two items.</p>
  
  <pre><code>Item                       ProdTotal    ProdCount
  Westvale Climbing Frame    1198         2
  </code></pre>
  
  <p>This order has two payment records held in the <code>tbl_payment</code> table, which is causing the double count. If I remove the payment table join it reports the correct figures, or if I select an order which has a single payment it works as well. Am I missing something, I am tired!!??</p>
  
  <pre><code>SELECT 
    txt_orditems_pname,
    SUM(mon_orditems_pprice) AS prodTotal,
    COUNT(uid_orderitems) AS prodCount
  FROM dbo.tbl_orders
    INNER JOIN dbo.tbl_orderitems ON (dbo.tbl_orders.uid_orders = dbo.tbl_orderitems.uid_orditems_orderid)
    INNER JOIN dbo.tbl_payment ON (dbo.tbl_orders.uid_orders = dbo.tbl_payment.uid_pay_orderid)
  WHERE
    uid_orditems_orderid = 61571
  GROUP BY
    dbo.tbl_orderitems.txt_orditems_pname
  ORDER BY
    dbo.tbl_orderitems.txt_orditems_pname
  </code></pre>
  
  <p>Any suggestions? </p>
  
  <p>Thank you.</p>
  
  <p>Drill down Table columns</p>
  
  <pre><code>dbo.tbl_payment.bit_pay_paid (1/0) Has this payment been paid, yes no
  dbo.tbl_orders.bit_order_archive (1/0) Is this order archived, yes no
  dbo.tbl_orders.uid_order_webid (integer) Web Shop's ID
  dbo.tbl_orders.bit_order_preorder (1/0) Is this a pre-order, yes no
  YEAR(dbo.tbl_orders.dte_order_stamp) (2012) Sales year
  dbo.tbl_orders.txt_order_status (varchar) Is the order dispatched, awaiting delivery
  dbo.tbl_orderitems.uid_orditems_pcatid (integer) Product category ID
  </code></pre>