I believe UNION ALL , instead of just UNION will select what you wish

----- Original Message -----
From: "Dennis McFall" <dennis@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, April 30, 2007 3:44 PM
Subject: Re: [firebird-support] Union statement automatically adds
'DISTINCT'?


> At 04:32 PM 4/30/2007, you wrote:
>>I have two statements:
>>
>>Select [fields] from [Table1]
>>Select [fields] from [Table2]
>>
>>The [Fields] are the same. Running them individually I get 39 records,
>>and 1 record. Running them together with a UNION between them I get 33
>>records. After some checking, it appears that what it thinks are
>>duplicates are being removed. I never specified the DISTINCT option for
>>the select statement though. Can anyone confirm that this is what
>>Firebord does?
>>
>
> My experience confirms yours. (FB 1.5). I found that I had to add
> another, otherwise immaterial, field to the selects in a UNION
> select (to distinguish two otherwise identical records), or I would
> not get all of the records I wanted. I don't know what the
> relational algebraic theory is....
>
> ./dm