Re: Union query dropping "duplicate" rows

From: Don Jellie (anonymous_at_discussions.microsoft.com)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 13:51:05 -0800

Vishal & Ray,

I'm so excited I'm going to the office right now to make the change.

Thank you!Thank you!Thank you!Thank you,
DJ

     
     ----- Ray Higdon wrote: -----
     
     Try UNION ALL, UNION ALL does not remove dupes
     
     --
     Ray Higdon MCSE, MCDBA, CCNA
     ---
     "Don Jellie" <anonymous@discussions.microsoft.com> wrote in message
     news:9A3F81D7-AAB4-4358-AF32-1C43DEEEF7A1@microsoft.com...
> Hi all,
>> I have 3 questions about the same issue. I a have union query across 2
     almost identical inventory tables (1 current year & 1 historical) that is
     dropping rows it thinks are duplicates. They're not dups, though. They
     just look like it. The real thing is massive, but a simplified example
     follows.
>> SQL:
> SELECT
> CLASS, ITEM, SUM(COUNT)
> FROM
> CURRENT_TBL
> GROUP BY
> CLASS, ITEM
> UNION
> SELECT
> CLASS, ITEM, SUM(COUNT)
> FROM
> HISTORY_TBL
> GROUP BY
> CLASS, ITEM
> Orde by 1, 2
>> Data:
> CLASS ITEM COUNT
> Fruit Apples 7
> Fruit Apples 100
> Veggies Peas 50
> Veggies Peas 50 (This row gets dropped!)
>> Questions:
> 1) Why is this happening and should I have expected it?
> 2) Is there syntax that will pull both the current and historical veggie
     rows even though they're identical?
> 3) I tried solving this by adding a unique column to each union (e.g.
     SELECT 'CUR' AS CUR, CLASS, ITEM, SUM(COUNT)) but I get an error on GROUP BY
     CUR, CLASS, ITEM. Can I not do that or is my syntax just bad?
>> Thanks a lot in advance,
> DJ
>

Relevant Pages