As far as i can tell the problem seems to be that you have multiple A and D records will the same polnum value. So I do not mean just one A record and one D record for a single polnum value but, for examples, 2 A records and 1 D record with the same polnum value.
On the second query this would just show as 1 on the count, but on your first SQL this would show as 2 as the SQL in the first statements joins all A records to all D records were the polnum is the same so in the example would return 2 records.
The way to check this is to execute the following SQL:
Select tran_typ, Polnum, count(*) from Tran_table
where (tran_typ = 'A' or tran_typ = 'D')
and Polnum like '%,%'
group by tran_typ, Polnum
having count(*) > 1
This will then show you all the occurrences of multiple A or D records for the same Polnum value.
Obviously, this may not be an incorrect situation as this may be what you intend for your data but it would explain the results you got from the two SQL statements.
Regards
Snib
|