...

View Full Version : disappearing records ???



Roelf
09-20-2007, 07:46 AM
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 :confused:

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 :confused:

guelphdad
09-20-2007, 01:19 PM
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
09-20-2007, 01:57 PM
ofcourse, i knew the difference between union and union all

but i did not realize the practical implications until now

Thanks a lot!

guelphdad
09-22-2007, 01:31 AM
no worries, sometimes it takes a different set of eyes to tell you what you already know. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum