SQL distinct query

Hello all,
I need to find Invoices that are not paid based on "Receipt = 0". One Invoice can have many orders. based on the query below I need to retrieve only 1 row per invoice.
I have this query =>
SELECT
  Distributor.DistributorID AS [Distributor DistributorID]
  ,Distributor.DistributorName
  ,Invoice.InvoiceID AS [Invoice InvoiceID]
  ,Invoice.[Date] AS [Invoice Date]
  ,Invoice.CustomerID AS [Invoice CustomerID]
  ,Invoice.Receipt
  ,Invoice.[Sum]
  ,Customer.CustomerID AS [Customer CustomerID]
  ,Customer.CustomerName
  ,InvoiceOrder.InvoiceOrderID
  ,InvoiceOrder.InvoiceID AS [InvoiceOrder InvoiceID]
  ,InvoiceOrder.OrderID AS [InvoiceOrder OrderID]
  ,InvoiceOrder.[Date] AS [InvoiceOrder Date]
  ,Customer.DistributorID AS [Customer DistributorID]
  ,[Order].OrderID AS [Order OrderID]
FROM
  [Order]
  INNER JOIN Customer
    ON Customer.CustomerID = [Order].CustomerID
  INNER JOIN InvoiceOrder
    ON [Order].OrderID = InvoiceOrder.OrderID
  INNER JOIN Distributor
    ON Distributor.DistributorID = Customer.DistributorID
 Where Invoice.Receipt = 0

thank you

kulpeAsked:
Who is Participating?
 
cyberkiwiCommented:
If I guess correctly, this should do it

This may produce more than 1 line per invoice - but only if the invoice is for multiple customers!
SELECT DISTINCT
  Distributor.DistributorID AS [Distributor DistributorID]
  ,Distributor.DistributorName
  ,Invoice.InvoiceID AS [Invoice InvoiceID]
  ,Invoice.[Date] AS [Invoice Date]
  ,Invoice.CustomerID AS [Invoice CustomerID]
  ,Invoice.Receipt
  ,Invoice.[Sum]
  ,Customer.CustomerID AS [Customer CustomerID]
  ,Customer.CustomerName
--  ,InvoiceOrder.InvoiceOrderID
--  ,InvoiceOrder.InvoiceID AS [InvoiceOrder InvoiceID]
--  ,InvoiceOrder.OrderID AS [InvoiceOrder OrderID]
--  ,InvoiceOrder.[Date] AS [InvoiceOrder Date]
--  ,Customer.DistributorID AS [Customer DistributorID]
--  ,[Order].OrderID AS [Order OrderID]
FROM
  Invoice
  INNER JOIN InvoiceOrder ON Invoice.InvoiceID = InvoiceOrder.InvoiceID
  INNER JOIN [Order] ON [Order].OrderID = InvoiceOrder.OrderID
  INNER JOIN Customer ON Customer.CustomerID = [Order].CustomerID
  INNER JOIN Distributor ON Distributor.DistributorID = Customer.DistributorID
 Where Invoice.Receipt = 0

Open in new window

0
 
cyberkiwiCommented:
Hi there,

Aren't you missing a table Invoice in the join?
0
 
kulpeAuthor Commented:
talk to me....
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kulpeAuthor Commented:
yes you right I missed copy&paste

INNER JOIN Invoice
    ON Invoice.InvoiceID = InvoiceOrder.InvoiceID
0
 
cyberkiwiCommented:
I see 4 tables

FROM [Order]
INNER JOIN Customer ...
INNER JOIN InvoiceOrder ...
INNER JOIN Distributor ...

Where does the table "Invoice" come from? Is that a working SQL statement?
0
 
kulpeAuthor Commented:
Yes it works.
The invoice is only for 1 customer, 1 distributer but for many orders.
0
 
kulpeAuthor Commented:
can you explain me the logic of the solution?? why did we remove the lines??
0
 
cyberkiwiCommented:
If you add a join to the lines, it will show you the line details, one for each line.

e.g.

Invoice ID, Invoice Amount
1, 2
3, 4
5, 6

IF Invoice ID 1 had 2 lines, the nature of SQL join is to show you all matches, so you get

Invoice ID, Invoice Amount, Line ID, Line Item
1, 2, 11, Boxes
1, 2, 12, Trinkets

But all you really care about is the invoices, so we remove the items that merely interfere.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.