CODEWITHSUNDEEP
sql
PowerMan2015
PowerMan2015

Reputation: 1418

3 tables linked by different fields

I cant quite get my head round an SQl statement im trying to write

Ive written an invoice system, i want to get the total amount of payments for ever invoice

I have 3 tables

InvoiceHeader, InvoiceDetail and Transactions

The two invoice tables are linked by a field called InvoiceRef. The transaction table is linked to invoicedetail by a field called TicketItemID

I allow the user to make payments against individual invoice items therefore there are multiple transactions for a single invoice items on a single invoice

I want to display the total amount of payments for every invoice in the invoiceHeader table

The following gets the total payments for an invoice

select  sum(Transactions.Amount)
from InvoiceDetail
 join Transactions
on InvoiceDetail.TicketItemID=Transactions.Reference
where InvoiceDetail.InvoiceRef= '000004'

However if i attempt to include it within a larger statement then i get an error. Here is the statement

select InvoiceHeader.InvoiceRef, 
InvoiceHeader.CustomerRef,
 InvoiceHeader.CompanyName,  
 InvoiceHeader.CreatedOn, 
 (SELECT    isnull(SUM(InvoiceDetail.Price * InvoiceDetail.Quantity * (InvoiceDetail.VATRate + 1 )),0) from InvoiceDetail where InvoiceDetail.InvoiceRef=InvoiceHeader.InvoiceRef) AS InvoiceTotal , 
 (SELECT isnull(sum(Amount),0)  from Transactions where Reference = InvoiceHeader.InvoiceRef) as AmountPaid,
  (Select sum(Transactions.Amount) from InvoiceDetail Join(Transactions) on InvoiceDetail.TicketItemID=Transactions.Reference where InvoiceDetail.InvoiceRef=InvoiceHeader.InvoiceRef) as ActualAmount,      
  invoiceheader.paid,       
  InvoiceHeader.Changed,      
   InvoiceHeader.New       
   From InvoiceHeader

the error is

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

Upvotes: 1

Views: 38

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35623

Use of correlated subqueries in the select clause is often a cause of performance issues, so I would suggest a different way of achieving your query. Note that the left joins you see below are use to mimic the effect of your existing subqueries, it is quite possible you don't need all of these as left joins and you should use inner joins where possible,

SELECT
        IH.InvoiceRef
      , IH.CustomerRef
      , IH.CompanyName
      , IH.CreatedOn
      , ISNULL(ID1.InvoiceTotal, 0) AS InvoiceTotal
      , ISNULL(T.AmountPaid, 0)     AS AmountPaid
      , ISNULL(ID2.ActualAmount, 0) AS ActualAmount
      , IH.paid
      , IH.Changed
      , IH.New
FROM InvoiceHeader IH
        LEFT OUTER JOIN
        (
                SELECT
                        InvoiceDetail.InvoiceRef
                      , SUM(InvoiceDetail.Price * InvoiceDetail.Quantity * (InvoiceDetail.VATRate + 1)) AS InvoiceTotal
                FROM InvoiceDetail
                GROUP BY
                        InvoiceDetail.InvoiceRef
        ) ID1 ON ID1.InvoiceRef = IH.InvoiceRef
        LEFT OUTER JOIN
        (
                SELECT
                        InvoiceDetail.InvoiceRef
                      , SUM(Transactions.Amount) AS ActualAmount
                FROM InvoiceDetail
                        INNER JOIN Transactions ON InvoiceDetail.TicketItemID = Transactions.Reference
                GROUP BY
                        InvoiceDetail.InvoiceRef
        ) ID2 ON ID2.InvoiceRef = IH.InvoiceRef
        LEFT OUTER JOIN
        (
                SELECT
                        Reference
                      , SUM(Amount) AS AmountPaid
                FROM Transactions
                GROUP BY
                        Reference
        ) T ON T.Reference = IH.InvoiceRef

Upvotes: 2

Harsh
Harsh

Reputation: 1319

You should remove parenthesis around transactions table, I believe:

select 
    InvoiceHeader.InvoiceRef, 
    InvoiceHeader.CustomerRef,
    InvoiceHeader.CompanyName,  
    InvoiceHeader.CreatedOn, 
    (SELECT    isnull(SUM(InvoiceDetail.Price * InvoiceDetail.Quantity * (InvoiceDetail.VATRate + 1 )),0) 
    from InvoiceDetail where InvoiceDetail.InvoiceRef=InvoiceHeader.InvoiceRef) AS InvoiceTotal , 
    (SELECT isnull(sum(Amount),0)  from Transactions 
    where Reference = InvoiceHeader.InvoiceRef) as AmountPaid,
    (Select sum(Transactions.Amount) from InvoiceDetail Join Transactions 
    on InvoiceDetail.TicketItemID=Transactions.Reference 
    where InvoiceDetail.InvoiceRef=InvoiceHeader.InvoiceRef) as ActualAmount,      
    invoiceheader.paid,       
    InvoiceHeader.Changed,      
    InvoiceHeader.New       
From InvoiceHeader

Upvotes: 2

Related Questions