DISTINCT SQL Statement

SELECT     tblInvoice.InvoiceNumber, SUM(tblInvoice.CommRepId)
FROM         tblPaymentItems RIGHT OUTER JOIN
                      tblInvoice ON tblPaymentItems.InvoiceNumber = tblInvoice.InvoiceNumber LEFT OUTER JOIN
                      tblCommissionTicketItems ON tblInvoice.InvoiceNumber = tblCommissionTicketItems.InvoiceNumber LEFT OUTER JOIN
                      tblPayments ON tblPaymentItems.PaymentID = tblPayments.PaymentID
WHERE     (tblInvoice.InvoiceBalance < .1) AND (tblInvoice.CommRepId = @ServiceRepId) AND (tblCommissionTicketItems.InvoiceComm IS NULL) AND
                      (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102)) AND (tblInvoice.InvoiceStatus <> 1) OR
                      (tblInvoice.InvoiceBalance < .1) AND (tblInvoice.CommRepId = @ServiceRepId) AND (tblCommissionTicketItems.InvoiceComm = 0) AND
                      (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102)) AND (tblInvoice.InvoiceStatus <> 1)
GROUP BY
tblInvoice.InvoiceNumber

Try this

SELECT tblInvoice.InvoiceNumber, tblInvoice.CommRepId
FROM tblInvoice
LEFT OUTER JOIN (
  SELECT DISTINCT InvoiceNumber
  FROM tblPaymentItems
  LEFT OUTER JOIN tblPayments ON tblPaymentItems.PaymentID = tblPayments.PaymentID
  WHERE  (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102))
  AND (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102))
) Pay ON Pay.InvoiceNumber = tblInvoice.InvoiceNumber
LEFT OUTER JOIN tblCommissionTicketItems ON tblInvoice.InvoiceNumber = tblCommissionTicketItems.InvoiceNumber
WHERE (tblInvoice.InvoiceBalance < .1)
      AND (tblInvoice.CommRepId = @ServiceRepId)
      AND (tblCommissionTicketItems.InvoiceComm IS NULL) AND
      AND (tblInvoice.InvoiceStatus <> 1)
      OR (tblInvoice.InvoiceBalance < .1)
      AND (tblInvoice.CommRepId = @ServiceRepId)
      AND (tblCommissionTicketItems.InvoiceComm = 0)
      AND (tblInvoice.InvoiceStatus <> 1)
ORDER BY tblInvoice.InvoiceDat

hi

>I have tried using DISTINCT  but can not get it to work.

i think that adding distinct should do it. the problem is probably in your where condition. i modified it, pls try

SELECT DISTINCT    tblInvoice.InvoiceNumber, tblInvoice.CommRepId
FROM         tblPaymentItems RIGHT OUTER JOIN
                      tblInvoice ON tblPaymentItems.InvoiceNumber = tblInvoice.InvoiceNumber LEFT OUTER JOIN
                      tblCommissionTicketItems ON tblInvoice.InvoiceNumber = tblCommissionTicketItems.InvoiceNumber LEFT OUTER JOIN
                      tblPayments ON tblPaymentItems.PaymentID = tblPayments.PaymentID
WHERE     (tblInvoice.InvoiceBalance < .1) AND (tblInvoice.CommRepId = @ServiceRepId) AND (ISNULL(tblCommissionTicketItems.InvoiceComm,0)=0) AND
                      (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102)) AND (tblInvoice.InvoiceStatus <> 1)
ORDER BY tblInvoice.InvoiceDate

                                              

1:
2:
3:
4:
5:
6:
7:
8:

the "question" is:
do you need any columns from the payments in the output?
  no  -> then why "join" ? / the distinct will work
  yes -> does it matter which row of the payments you need to show up?
    yes/no -> just use a method like shown above to "select" the payment row you need
    all of them -> you mean: group by invoice, and sum up / count the payments.
        code a GROUP BY and put SUM() on the payments sum field, for example-

hope this clarifies, otherwise explain what you are missing.

@angelIII: hi, i think the join is for the where condition.

angellll,

No I don't need any of the payment columns in the output but I do need to only have the records that have a payment after  2007-10-14. Am I doing this correctly or not

John

if you don't need payment columns, do either of the 2 suggested codes:

use distinct/group by:

SELECT     DISTINCT tblInvoice.InvoiceNumber, tblInvoice.CommRepId
FROM         tblPaymentItems RIGHT OUTER JOIN
                      tblInvoice ON tblPaymentItems.InvoiceNumber = tblInvoice.InvoiceNumber LEFT OUTER JOIN
                      tblCommissionTicketItems ON tblInvoice.InvoiceNumber = tblCommissionTicketItems.InvoiceNumber LEFT OUTER JOIN
                      tblPayments ON tblPaymentItems.PaymentID = tblPayments.PaymentID
WHERE     (tblInvoice.InvoiceBalance < .1) AND (tblInvoice.CommRepId = @ServiceRepId) AND (tblCommissionTicketItems.InvoiceComm IS NULL) AND
                      (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102)) AND (tblInvoice.InvoiceStatus <> 1) OR
                      (tblInvoice.InvoiceBalance < .1) AND (tblInvoice.CommRepId = @ServiceRepId) AND (tblCommissionTicketItems.InvoiceComm = 0) AND
                      (tblPayments.PaymentDate > CONVERT(DATETIME, '2007-10-14 00:00:00', 102)) AND (tblInvoice.InvoiceStatus <> 1)
ORDER BY tblInvoice.InvoiceDate 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10: