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