I'm having an issue with a join and duplicating records. I can't seem to figure this out.

Any help would be appreciated.

Thanks in advance

----------------------------------------------

SELECT DISTINCT DOCUMENT, INVOICENO, INVOICEDATE, COST, SELL, SEQ, X
FROM BilledInvoices
WHERE (DOCUMENT = '000039111')
ORDER BY DOCUMENT, SEQ

-----------------------------------------------

Shows the Following with is correct - There are 4 records

DOCUMENT INVOICENO INVOICEDATE COST SELL SEQ X
-----------------------------------------------------------------------
000039111 123456 01/01/12 1.00 2.00 1 X
000039111 123456 01/01/12 2.00 3.00 2 X
000039111 123456 01/01/12 3.00 4.00 3 X
000039111 123456 01/01/12 4.00 5.00 4 X




Now, I want to join the above table with another table to get a final code number associated with each line item. Query is below

SELECT DISTINCT
BilledInvoices.DOCUMENT, BilledInvoices.INVOICENO, BilledInvoices.INVOICEDATE, BilledInvoices.COST, BilledInvoices.SELL,
BilledInvoices.SEQ, BilledInvoices.X, DISP.FINALCODE
FROM BilledInvoices INNER JOIN
DISP ON BilledInvoices.DOCUMENT= DISP.DOCUMENT
WHERE (BilledInvoices.DOCUMENT= '000039111')
ORDER BY BilledInvoices.DOCUMENT, BilledInvoices.SEQ


Now, this query returns 16 records instead of the 4. how can I eliminate the duplicate records ? There should only be 4 records total.



Thanks

Can you show the rows from DISP where DOCUMENT = '000039111' ?

You will probably need to either: 1) add another field in the join or 2) join to a derived table (instead of DISP) which selects one row per DOCUMENT from DISP.

Here is what I would like it to look like...

DOCUMENT INVOICENO INVOICEDATE COST SELL SEQ X DISP
000039111 123456 01/01/12 1.00 2.00 1 X 1000
000039111 123456 01/01/12 2.00 3.00 2 X 1001
000039111 123456 01/01/12 3.00 4.00 3 X 1002
000039111 123456 01/01/12 4.00 5.00 4 X 1003


--------------------------------------------------------------------------------------

Here is what I'm getting after the join

DOCUMENT INVOICENO INVOICEDATE COST SELL SEQ X DISP
000039111 123456 01/01/12 1.00 2.00 1 X 1000
000039111 123456 01/01/12 2.00 3.00 2 X 1001
000039111 123456 01/01/12 3.00 4.00 3 X 1002
000039111 123456 01/01/12 4.00 5.00 4 X 1003
000039111 123456 01/01/12 1.00 2.00 1 X 1000
000039111 123456 01/01/12 2.00 3.00 2 X 1001
000039111 123456 01/01/12 3.00 4.00 3 X 1002
000039111 123456 01/01/12 4.00 5.00 4 X 1003
000039111 123456 01/01/12 1.00 2.00 1 X 1000
000039111 123456 01/01/12 2.00 3.00 2 X 1001
000039111 123456 01/01/12 3.00 4.00 3 X 1002
000039111 123456 01/01/12 4.00 5.00 4 X 1003
000039111 123456 01/01/12 1.00 2.00 1 X 1000
000039111 123456 01/01/12 2.00 3.00 2 X 1001
000039111 123456 01/01/12 3.00 4.00 3 X 1002
000039111 123456 01/01/12 4.00 5.00 4 X 1003

what is duplicated in table DISP? can you use a second (AND) criteria for the join?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

sorry, the DISP field in the DISP table. once adding that field, I get the duplicates. I don't have another field currently to do a second AND in my join

Sorry, I meant finalcode in the DISP table

The problem is that you are joining to the DISP table, but your join is not tight enough. i.e. You need another column to join on to find the distinct record to join to. because the DISP table has four records with the same document number, the join will always result in multiple returns if you do not add another column. It looks to me like the SEQ column would be the best bet. Try something like:

SELECT DISTINCT
BilledInvoices.DOCUMENT, BilledInvoices.INVOICENO, BilledInvoices.INVOICEDATE, BilledInvoices.COST, BilledInvoices.SELL,
BilledInvoices.SEQ, BilledInvoices.X, DISP.FINALCODE
FROM BilledInvoices INNER JOIN
DISP ON BilledInvoices.DOCUMENT= DISP.DOCUMENT
AND BilledInvoices.SEQ = DISP.SEQ
WHERE (BilledInvoices.DOCUMENT= '000039111')
ORDER BY BilledInvoices.DOCUMENT, BilledInvoices.SEQ

If SEQ is not the correct column, then just like JonFor said earlier...we need to see what the COMPLETE layout and some sample/dummy data for the two tables to help you find the best join.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

Thank you everyone for your help..

After looking a little at my table structure I was able to add a few more fields and get things linked correctly.

Thanks again