Odd display result using UNION statement

[james0816]

ok....now this is just weird...

i have two scripts against two seperate tables. Same number of columns defined.

first script returns 97 records. second returns 3

When I combine the two using a UNION, i get 99 records.

Split the scripts and I get 97 and 3 again.

Can anyone maybe shed a little light on what may be happening here or find out what record is missing from the the UNION statement?

strange.........

 

[hoinz]

It seems there is one row in both result sets, in the 97-row-set and in the 3-row-set.
UNION will give this row just once.
If you need it twice, try UNION ALL.

hope this helps

 

[james0816]

thx...i was just doing a side by side comparison. i found one particular grouping to have 29 records in the first query but only 28 in the UNION query.

on the first query, it found one field to have two entries:

field1
2030445
2030445

on the union query, it only displays it once even though both records are needed.

field1
2030445

the records in question are all from the first result set not the second. this just adds to my confusion.

i put in the UNION ALL and i got 100.

 

[hoinz]

Ok,
my first idea was not quite correct, obviously.

UNION will remove duplicates, no matter where they come from!
You learn something new every day ...