disappearing records ???

[Roelf]

Hi,

I have this problem i cannot explain why it is happening. So perhaps one of you can.

I made a query to return records from a table with a few joins to get extra information. This query returns 151597 records.

I have put this query in a view so it can be accessed easy using excel for creating pivottables. The view returns 151597 records. All is fine.

Now i have added a query to another table pulling more records, combining them using UNION keyword. The first recordset is identified with a statusfield with a certain value, the second recordset has another value for this statusfield.

When i run this view and use a filter to get all the records from the first recordset, it suddenly returns 150086 records. 1511 less

so i did this in query analyser:

select count(*) from
(
   select 'shipped' as status, someotherfields
   from table1
) as subselect
where status = 'shipped'

returned the correct number of records (as much records as there are in table1)

select count(*) from
(
   select 'shipped' as status, someotherfields
   from table1
   union
   select 'contracted' as status, someotherfields
   from table2
) as subselect
where status = 'shipped'

returned the wrong number of records (less than previous)

how is that possible????? adding records with status 'contracted' will reduce the number of records with status 'shipped'???

really confused here

[guelphdad]

A UNION removes duplicates

say you have values A, B, C, D in table one and values A and D in table two.

a UNION returns only 4 rows. If you want all values for A and D returned you need a UNION ALL which returns A, A, B, C, D, D

[Roelf]

ofcourse, i knew the difference between union and union all

but i did not realize the practical implications until now

Thanks a lot!

[guelphdad]

no worries, sometimes it takes a different set of eyes to tell you what you already know.