SQL statement returning duplicates with DISTINCT [modified]
-
Does anybody know why this SQL statement returns duplicates even though I have included the DISTINCT keyword. There aren't duplicates in the database.
SELECT DISTINCT BLT_BILLM_TAX.BILLM_TAX_UNO, BLT_BILLM_TAX.BILL_TRAN_UNO, HBM_MATTER.MATTER_CODE, BLT_BILLM_TAX.JURISDIC_CODE,BLT_BILLM_TAX.TAX_CODE, BLT_BILLM_TAX.TAXABL_FEES_AMT, BLT_BILLM_TAX.TAXABL_HARD_AMT, BLT_BILLM_TAX.TAXABL_SOFT_AMT,
BLT_BILLM_TAX.TAX_ON_FEES_AMT, BLT_BILLM_TAX.TAX_ON_HARD_AMT, BLT_BILLM_TAX.TAX_ON_SOFT_AMT, BLT_BILLM_TAX.FEE_TAX_RATE,
BLT_BILLM_TAX.DISB_TAX_RATE, CDT_DISB.PERIOD, CDT_DISB.COST_CODE, BLT_BILL.BILL_NUM
FROM HBM_MATTER
INNER JOIN CDT_DISB ON HBM_MATTER.MATTER_UNO = CDT_DISB.MATTER_UNO
INNER JOIN BLT_BILLM_TAX ON CDT_DISB.BILL_TRAN_UNO = BLT_BILLM_TAX.BILL_TRAN_UNO
INNER JOIN BLT_BILL ON CDT_DISB.BILL_TRAN_UNO = BLT_BILL.TRAN_UNO
INNER JOIN APT_INVOICE ON CDT_DISB.SOURCE_TRAN_UNO = APT_INVOICE.TRAN_UNO
WHERE (APT_INVOICE.PERIOD BETWEEN '200601' AND '200601')
AND (APT_INVOICE.TRAN_UNO = 627167)Any help would be appreciated Thanks -- modified at 6:00 Thursday 31st May, 2007 -- modified at 6:01 Thursday 31st May, 2007
There are 10 types of people in the world, those who understand binary and those who dont.
-
Does anybody know why this SQL statement returns duplicates even though I have included the DISTINCT keyword. There aren't duplicates in the database.
SELECT DISTINCT BLT_BILLM_TAX.BILLM_TAX_UNO, BLT_BILLM_TAX.BILL_TRAN_UNO, HBM_MATTER.MATTER_CODE, BLT_BILLM_TAX.JURISDIC_CODE,BLT_BILLM_TAX.TAX_CODE, BLT_BILLM_TAX.TAXABL_FEES_AMT, BLT_BILLM_TAX.TAXABL_HARD_AMT, BLT_BILLM_TAX.TAXABL_SOFT_AMT,
BLT_BILLM_TAX.TAX_ON_FEES_AMT, BLT_BILLM_TAX.TAX_ON_HARD_AMT, BLT_BILLM_TAX.TAX_ON_SOFT_AMT, BLT_BILLM_TAX.FEE_TAX_RATE,
BLT_BILLM_TAX.DISB_TAX_RATE, CDT_DISB.PERIOD, CDT_DISB.COST_CODE, BLT_BILL.BILL_NUM
FROM HBM_MATTER
INNER JOIN CDT_DISB ON HBM_MATTER.MATTER_UNO = CDT_DISB.MATTER_UNO
INNER JOIN BLT_BILLM_TAX ON CDT_DISB.BILL_TRAN_UNO = BLT_BILLM_TAX.BILL_TRAN_UNO
INNER JOIN BLT_BILL ON CDT_DISB.BILL_TRAN_UNO = BLT_BILL.TRAN_UNO
INNER JOIN APT_INVOICE ON CDT_DISB.SOURCE_TRAN_UNO = APT_INVOICE.TRAN_UNO
WHERE (APT_INVOICE.PERIOD BETWEEN '200601' AND '200601')
AND (APT_INVOICE.TRAN_UNO = 627167)Any help would be appreciated Thanks -- modified at 6:00 Thursday 31st May, 2007 -- modified at 6:01 Thursday 31st May, 2007
There are 10 types of people in the world, those who understand binary and those who dont.
-
Most likely it is a inner join issue. If there are multiple rows in the table you are joining to, but you are not including any columns from that join you will get duplicates. I would look at removing any unnecessary joins. Hope that helps. Ben