Reputation: 12431

SQL Server SQL JOIN Assistance

I'm doing joins incorrectly, and haven't figured out the proper way to get the data I need given the 3 tables below (Customer, SalesHeader, SalesDetail) I'm trying to get line items with 1 Customer per line and the sum of all Sales Details that are in GL Acct 4000 and all Sales Deails in GL Acct 5000. There are many more columns, and several other GL Accounts I want to add, but I've broken it down to the simplest form to try to get it to work. I've provide mock data below and the lastest version of the SQL Statement I had unsuccessfully worked on. It would be greatly appreciated if someone could help me figure out what I'm doing wrong in this SQL Select Statement.

Customer

CustID | CustCode
------------------
1      | AAA111
2      | AN8348

SalesHeader

SH_ID |  SH_CustID  | SH_GLACCT
-------------------------------
1     |  1          | 4000
2     |  1          | 5000
3     |  1          | 4000
4     |  2          | 5000

SalesDetail

SD_ID | SD_HID  | Price
--------------------------
1     | 1       | 100.00
2     | 1       | 540.00
3     | 2       | 100.00
4     | 3       | 600.00
5     | 4       | 50.00
6     | 4       | 75.00

Desired OUTPUT

Carpet = 4000 Pad = 5000

CustID | CustCode    | Carpet (Sum all SH_GLACCT = 4000) | PAD (Sum all SH_GLACCT = 5000)
-------------------------------------------------------------------------------------------
1      | AAA111      | 1240.00                           | 100.00
2      | AN8348      | 0.00                              | 125.00

Incorrect SQL (Both Amounts are over what they should be)

SELECT C.CustID, C.CustCode, SUM(ADH.Price) AS Carpet, SUM(APD.Price) As Pad
FROM Customer AS C

LEFT OUTER JOIN SalesHeader AS ACH On C.CustID = ACH.SH_CustID AND ACH.SH_GLACCT = '4000'
LEFT OUTER JOIN SalesDetail AS ADH On ACH.SH_ID = ADH.SD_HID


LEFT OUTER JOIN SalesHeader AS APH On C.CustID = APH.SH_CustID AND APH.SH_GLACCT = '5000'
LEFT OUTER JOIN SalesDetail AS APD On APH.SH_ID = APD.SD_HID


GROUP BY C.CustID, C.CustCode

Upvotes: 3

Answers (4)

Beth

Reputation: 9617

SELECT C.CustID, C.CustCode, SH_GLACCT, SUM(Price) AS sum_price
FROM Customer  C
INNER JOIN SalesHeader sh On C.CustID = sH.SH_CustID 
LEFT OUTER JOIN SalesDetail sd On sh.SH_ID = sd.SD_HID

WHERE
SH_GLACCT in(4000,5000)

GROUP BY CustID, CustCode, SH_GLACCT

Upvotes: 0

mancaus

Reputation: 3011

You can use the PIVOT operator for this:

SELECT CustID, CustCode, SUM([4000]) Carpet, SUM([5000]) PAD
FROM Cust c
JOIN SalesHeader sh ON c.CustID = sh.SH_CustID
JOIN SalesDetail sd ON sh.SH_ID = sd.SD_HID
PIVOT (
    SUM(sd.Price)
    FOR sh.SH_GLACCT IN ([4000],[5000])
) AS pt
GROUP BY CustID, CustCode

Upvotes: 0

Zephyr

Reputation: 7823

Try something like the following:

SELECT  c.CustID
    ,   c.CustCode
    ,   Carpet  = SUM(CASE WHEN sh.SH_GLACCT = 4000 THEN sd.Price ELSE 0 END)
    ,   Pad     = SUM(CASE WHEN sh.SH_GLACCT = 5000 THEN sd.Price ELSE 0 END)
FROM    Customer c
    LEFT JOIN
        SalesHeader sh
    ON c.CustID = sh.CustID

    LEFT JOIN 
        SalesDetail sd
    ON  sh.sh_id = sd.sd_hid
GROUP   BY
        c.CustID
    ,   c.CustCode

Upvotes: 1

Charles Bretana

Reputation: 146567

Try This:

   Select c.CustId, c.CustCode
       Sum(Case When h.SH_GLACCT = 4000 Then Price End) Acct4000Total,
       Sum(Case When h.SH_GLACCT = 5000 Then Price End) Acct5000Total
   From Customer c
      Join Salesheader h On h.SH_CustID = c.CustID
      Join SalesDetail d On d.SD_HID = h.SH_ID
   Where h.SH_GLACCT In (4000, 5000)
   Group By c.CustId

if you want to list the customers with no sales then use outer join:

   Select c.CustId, c.CustCode
       Sum(Case When h.SH_GLACCT = 4000 Then Price End) Acct4000Total,
       Sum(Case When h.SH_GLACCT = 5000 Then Price End) Acct5000Total
   From Customer c
      Left Join (Salesheader h Join SalesDetail d 
                   On d.SD_HID = h.SH_ID
                       And h.SH_GLACCT In (4000, 5000))
          On h.SH_CustID = c.CustID
   Group By c.CustId

Upvotes: 4