NOTE: If this is too long, scroll to the last paragraph where hopefully I've summarized my problem.
I have a simple problem. I have two tables, one is for the "header" and one is for the "details" of the header. I need a view to return all details and the corresponding header info.
Here are the tables:
Here is the query I am using for the view, which is returning duplicate results:
SELECT dbo.Detail.DiscountAmount, dbo.Detail.NetAmount, dbo.Detail.GrossAmount, dbo.Detail.ReferenceInfo, dbo.Detail.InvoiceDate, dbo.Header.PaymentAmount, dbo.Header.PaymentDate, dbo.Header.PaymentNumber, dbo.Header.VendorID, dbo.Header.VendorName, dbo.Detail.ProcessedToCsv, dbo.Detail.ReferenceNumber, dbo.Detail.ReferenceDescription FROM dbo.Detail INNER JOIN dbo.Header ON dbo.Detail.ChequePaymentNumber = dbo.Header.PaymentNumber
The above is no good, the view returns duplicate results. EX:
I have tried using the DISTINCT keyword, and that does not return ALL of the results.
What do I need to do to solve my problem?
If the above does not make sense, maybe a simpler example. Suppose I have one table with people's names, and another with the cars they own. I want the following returned:
Bob | Civic, red, 2007
Bob | Focus, white, 2009
Tim | X5, silver, 2008
Would appreciate any help with this.