Reputation: 701

SQL - Join multiple table

I have four tables Customer, Sales, Invoice, and Receipt.

Customer

ID      Name
1         A

Sales

ID     Name
1      Ben

Invoice

ID     Amt      Date         CustomerID       SalesID
1      12       1/9/2014       1                 1
2      10       1/10/2014      1                 1
3      20       2/10/2014      1                 1
4      30       3/10/2014      1                 1

Receipt

ID     Amt      Date         CustomerID       SalesID
1      10       4/10/2014        1               1

I wish to join those 4 table as below with sum up the Ammount(s), but I am stuck as to how I can achieve my desired

RESULT

CustomerID         SalesID         Inv_Amt       Rep_Amt       Month  
   1                  1               12            0            9
   1                  1               60            10           10

I've been stuck for days. But, I have no idea how to proceed.

Upvotes: 2

Answers (3)

Upendra Chaudhari

Reputation: 6543

You can get month wise total receipt and invoice amount by grouping and sub query like below :

SELECT  Invoice.CustomerID [CustomerID], 
        Invoice.SalesID [SalesID], 
        SUM(Invoice.Amt) [Invoice_Amt], 
        ISNULL((SELECT SUM(Amt) 
                    FROM Receipt 
                    WHERE CustomerID = Invoice.CustomerID 
                    AND SalesID = Invoice.SalesID 
                    AND Month(Date) = Month(Invoice.Date)),0) [Receipt_Amt],
        MONTH(Invoice.Date) Month
FROM    Invoice
GROUP BY Invoice.CustomerID, Invoice.SalesID, MONTH(Invoice.Date)

SQL Fiddle Demo1

Warning : Here data will come for all months which are in Invoice table. If for any month, there is no any data in invoice table then no result will come for that month even for receipt also.

UPDATE: To get result from all months of invoice and receipt table, you need to get it using CTE as like below :

;with CTE as
(
    SELECT Invoice.CustomerID, Invoice.SalesID, MONTH(Invoice.Date) MonthNo FROM Invoice
    UNION
    SELECT Receipt.CustomerID, Receipt.SalesID, MONTH(Receipt.Date) MonthNo FROM Receipt
)


SELECT  CTE.CustomerID [CustomerID], 
        CTE.SalesID [SalesID], 
        ISNULL((SELECT SUM(Amt) 
                    FROM Invoice
                    WHERE CustomerID = CTE.CustomerID 
                    AND SalesID = CTE.SalesID 
                    AND Month(Date) = CTE.MonthNo),0) [Invoice_Amt],
        ISNULL((SELECT SUM(Amt) 
                    FROM Receipt 
                    WHERE CustomerID = CTE.CustomerID 
                    AND SalesID = CTE.SalesID 
                    AND Month(Date) = CTE.MonthNo),0) [Receipt_Amt],
        MonthNo
FROM    CTE

SQL Fiddle Demo2

Upvotes: 1

Sebastian Osuna

Reputation: 397

Looks like a homework, but ...

SELECT 
  Customer.ID AS CustomerID, 
  Sales.ID AS SalesID, 
  Invoice.Amt AS Inv_Amt, 
  Receipt.Amt AS Rep_Amt, 
  MONTH(Invoice.Date) AS Month 
FROM 
  Customer 
  INNER JOIN Receipt ON Customer.ID = Receipt.CustomerID 
  INNER JOIN Invoice ON Customer.ID = Invoice.CustomerID 
  INNER JOIN Sales ON Sales.ID = Receipt.SalesID

I didn't bother checking the result is what you expect, but the query should be something like that. You can play with the join conditions in order to get the result.

Upvotes: 0

Mureinik

Reputation: 311018

Frankly, since you're just selecting customer and sales IDs (as opposed to names), you don't even need to joint all four tables:

SELECT   i.CustomerID, 
         i.SalesID, 
         SUM(i.Amt) AS InvAmt, 
         SUM(r.Amt) AS RepAmt, 
         MONTH(i.`Date`) AS `Month`
FROM     Invoice i
JOIN     Receipt r ON i.CustomerID = r.CustomerID AND 
                      i.SalesID = r.SalesID AND
                      MONTH(i.`Date`) = MONTH(r.`Date`)
GROUP BY i.CustomerID, i.SalesID, MONTH(i.`Date`) AS `Month`