Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    disappearing records ???

    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:
    Code:
    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)

    Code:
    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
    I am the luckiest man in the world

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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

  • Users who have thanked guelphdad for this post:

    Roelf (09-20-2007)

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    ofcourse, i knew the difference between union and union all

    but i did not realize the practical implications until now

    Thanks a lot!
    I am the luckiest man in the world

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    no worries, sometimes it takes a different set of eyes to tell you what you already know.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •