Union Query...

[vbjohn]

I was wondering on why I loose some data when I do a union query?

When I run the first query I get 15 Records and then when I run the second query I got 1 Record (which
is good). The when I run them in a Union Query I get a total of 10 Records instead of 16.


===UNION QUERY EXAMPLE==================================

SELECT GL20000.REFRENCE, GL20000.TRXDATE, GL20000.DEBITAMT, GL00100.ACTNUMBR_3,BIENBUDG.DESCRIPTN,BIENBUDG.DEPARTMNT
FROM GL00100 INNER JOIN
GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX INNER JOIN
BIENBUDG ON BIENBUDG.DEPARTMNT = GL00100.ACTNUMBR_2
WHERE GL00100.ACTNUMBR_3 > '2000' AND BIENBUDG.DEPARTMNT > '019' AND YEAR(GL20000.TRXDATE) BETWEEN '2001'
AND '2002' AND GL20000.DEBITAMT <> '0'
UNION
SELECT GL30000.REFRENCE, GL30000.TRXDATE, GL30000.DEBITAMT, GL00100.ACTNUMBR_3,BIENBUDG.DESCRIPTN,BIENBUDG.DEPARTMNT
FROM GL00100 INNER JOIN
GL30000 ON GL00100.ACTINDX = GL30000.ACTINDX INNER JOIN
BIENBUDG ON BIENBUDG.DEPARTMNT = GL00100.ACTNUMBR_2
WHERE GL00100.ACTNUMBR_3 > '2000' AND BIENBUDG.DEPARTMNT > '019' AND YEAR(GL30000.TRXDATE) BETWEEN '2001'
AND '2002' AND GL30000.DEBITAMT <> '0'
ORDER BY BIENBUDG.DEPARTMNT, GL00100.ACTNUMBR_3, GL20000.TRXDATE

=======================================================

Any ideas or suggestions would be great.

John-

 

[tlbroadbent]

By Default, SQL Server eliminates duplicates in a result set of a Union query. To inhibit this behavior and the word ALL after UNION.

<query>
UNION ALL
<query> Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[vbjohn]

I just tried it and still got the same data.

 

[vbjohn]

Got It...I figured out what I did...

Thanks!!!!!!!!