>Hi,
>
>The following is part of an SQL union, the problem is that I am getting two duplicate records instead of one.
>
>As far as I was aware UNION enliminates duplicates, whereas UNION ALL with give you all records
>
>Both tables are identical i.e. a main transaction file and an archive file
>
>
>Select Table1.field1, COUNT(*) as total ;
>FROM Table1 ;
>GROUP BY 1 ;
>UNION ;
>select Table2.field1, COUNT(*) as total ;
>FROM Tabel2 ;
>GROUP BY 1 ;
>order by 1 ;
>INTO Cursor curCursor1 nofilter
>
>
>Thanks in advance
Neil,
Union eliminates duplicate records - not duplicate fields
If one table is an archive and does not contain anything that is in Table2, would the following not make more sense ?
select Field1, ;
sum(Total) as Total ;
from ( ;
select Field1, ;
cnt(*) as Total ;
from Table1 ;
group by 1;
union all ;
select Field1, ;
cnt(*) as Total ;
from Table2 ;
group by 1;
) xxx ;
group by 1
Gregory