SQL JOIN help for SQL Server

Question

I am making the wrong connections, and have not figured out how to get the data that I need correctly, given the 3 tables below (Customer, SalesHeader, SalesDetail). I am trying to get positions with 1 customer per line and the sum of all sales data that are in GL Acct 4000 and all sales products in GL Acct 5000. There are many more columns and several other GL accounts that I want to add, but I broke them to the simplest form try to make it work. I provide the mock data below and the latest version of SQL Statement, which I have unsuccessfully worked with. It would be very helpful if someone could help me understand what I am 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

SQL ( , )

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

Answer 1

:

   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  

, :

   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  

Answer 2

:

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

Answer 3

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

Answer 4

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