View for 2 tables, duplicate results returned

[dfn77]

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.

EX:
header1 details1
header1 details2
header1 details3
header2 details1
header2 details2
...

Here are the tables:

Detail
ID
HeaderID
Created
Tag
ReferenceNumber
ReferenceDescription
InvoiceDate
NetAmount
GrossAmount
DiscountAmount
ReferenceInfo
ProcessedToCsv
ChequePaymentNumber

Header
ID
Paid
Created
PaidDate
PaymentAmount
PaymentDate
PaymentNumber
VendorID
VendorName

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:
header1 details1
header1 details1

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.

[adam_k]

Why are you using

dbo.Detail.ChequePaymentNumber = dbo.Header.PaymentNumber

when you are having Header.ID and Detail.HeaderID ?

[dfn77]

When my program inserts details into the DB, each detail (invoice) belongs to a header (cheque). So, when I read an invoice, I see which cheque it is in, and enter that into the DB (ChequePaymentNumber in detail record) in the details table. Headers and Details are read separately from a file.

Similarly, when I read cheques, I look at the payment number, and enter that into the DB (PaymentNumber in Header table).

I stopped using Header.ID and Detail.HeaderID when I was trying to empty out the tables and reset the seed for the unique key. Since I was doing this frequently, it was easier to just eliminate the foreign key constraint. Also, it simplifies reading cheques/invoices from the file. There is no "ID" in the file I read, only payment number. So, it was more complicated to read headers and details individually, then sort out the right ID number for all details. It was quicker and simpler to just use the built-in "ID" from the file, which is the Payment Number.

[adam_k]

Yes but is detail.HeaderID something different than Header.ID?
And by

Similarly, when I read cheques, I look at the payment number

you mean ChequePaymentNumber, right?

Could you post the result from the following query?

select 'Header', count(*) as 'rows' from Header
where PaymentNumber in (select distinct ChequePaymentNumber from Detail) --need this as it's an inner join
union select 'Detail', count(*) from Detail 
where ChequePaymentNumber in (select distinct PaymentNumber from Header) --need this as it's an inner join 
union select 'Join', count(*) from (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 ) a

[dfn77]

Thanks for your continued help.

Yes but is detail.HeaderID something different than Header.ID?
And by you mean ChequePaymentNumber, right?

Right now, detail.HeaderID and header.ID are different because I am not manually populating detail.HeaderID. So right now, they are irrelevant.

From a file, I read headers (cheques) and details (invoices). In the file, when reading a header (cheque), and I come across Payment Number, I put it in Header.PaymentNumber. Again, in the file, when reading a detail (invoice), and I come across Payment Number, I put it in Detail.ChequePaymentNumber. If, for some reason, there are details with ChequePaymentNumbers that don't correspond to any PaymentNumbers in header, that is OK, not my problem.

Could you post the result from the following query?

...

Yes, of course!

The results:
Header | 214
Detail | 2537
Join | 4521

[adam_k]

Friend, the results you are posting are not showing a cartesian product between the 2 tables, but for a portion of their records.

Could it be that you are storing NULL or '' (blank) as PaymentNumber & ChequePaymentNumber? Try adding this:

where ltrim(dbo.Detail.ChequePaymentNumber) != '' and dbo.Detail.ChequePaymentNumber is not NULL
and ltrim(dbo.Header.PaymentNumber) ='' 
and dbo.Header.PaymentNumber is not NULL

Also please check if you are having duplicate values in PaymentNumber. (I've never dealt with cheques this way, always I've used an ID of my own so this might be silly to ask, but I don't know the data you are working with -including the source of your file)

[dfn77]

Wow.
OK.
After your post, I went back to the source and asked them to verify their text files.
After going over the data they gave me, it seems some of it was off (in the text file). I received a new text file, and now, your query returns the following:
Header | 148
Detail | 1469
Join | 1469

That seems correct.
My original query also returns everything, no duplicates.

When I add your last query (just the WHERE clause) to mine, I get 0 results.

Can I assume my original query is OK?? It seems it is alright now, that they gave me a proper text file.

Duplicate payment numbers in details is OK. Many detail records may have the same payment number (ChequePaymentNumber in Detail). Duplicate payment numbers are NOT OK for the Header (PaymentNumber in Header).

[adam_k]

I just saw where I went wrong with the where clause I gave you (I forgot a ! in the , but your query should be just fine - according to the number of records you are posting.

May I hint you that if you already got a wrong file once, you should add checkpoints that will ensure that you are not processing the wrong file again?
This can be as easy as assigning Header.PaymentNumber as key.

[dfn77]

Yes, I will do that. And more than that from now on haha

Thank you very much for your help!

[adam_k]

If your problem has been solved, please mark this thread as solved.