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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Hi there,

Aren't you missing a table Invoice in the join?
0
kulpeAuthor Commented:
talk to me....
0
kulpeAuthor Commented:
yes you right I missed copy&paste

INNER JOIN Invoice
    ON Invoice.InvoiceID = InvoiceOrder.InvoiceID
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.