Records not returned by UNION query

[Aqif]

Hi

I have two queries

1. SELECT ADMDIAG AS ReadCode FROM TblSurgicalForm WHERE ADMDIAG Is Not Null

Returns me 1235 records

2. SELECT ADMDIAG2 AS ReadCode FROM TblSurgicalForm WHERE ADMDIAG2 Is Not Null

Returns me 133 records

But when I try to combine them in to one query like

SELECT ADMDIAG AS ReadCode FROM TblSurgicalForm WHERE ADMDIAG Is Not Null

UNION

SELECT ADMDIAG2 AS ReadCode FROM TblSurgicalForm WHERE ADMDIAG2 Is Not Null

It returns me 518 records. In my view it should return me 12385+133=1368 records

Where I am doing wrong..any help appreciated


Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[Aqif]

Hi,

Just saw some help and came to know that UNION query will automatically remove duplicates. So t saves me one step



Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[OhioSteve]

If you want to keep the duplicates, use "union all...