Re: SQL UNION of two tables -Reply

[LYNN FRANCIS <Lfrancis@ABA.COM>]

The Oracle MINUS function will result in the rows from each table where both the key (product) and qty are the same being removed the remainder will be the rows that do not match in their entirety.  The rows in a MINUS 
are evaluated across all the columns.  
This action might be of use to you but you have to remember the the result is not based on the key alone.

>>> "William G. Northen" <wgn3c@VIRGINIA.EDU> 11/18/97 09:00am >>>
Isnt there a SQL Minus function to get the difference between two tables?

        like
                select  product,
                         qty
                from table2
                MINUS
                         table1
        go



At 16:33 11/17/97 -0700, Robert Goshko wrote:
>By the looks of what you are trying to do, you want an outer join query
between tables 1 and 2 if you wanted to do it with SQL, a union might work
but you would have to do break processing when the product changed, and then
in your sort, the greater value is either going to come first or last.
>
>As for the outer join, doing tat will really depend on your database system.
>
>The other option is an array.
>
>Later...
>      Rob
>
>-----------------------------------------------------------
>Thesaurus: A verbose member of the extinct dinosaur family
>-------------------+-------------------+-------------------
>   Robert Goshko   |  rgoshko@ibm.net  |  (403) 417-AXIS
> Axis Developments |                   | Sherwood Park, AB
>
>-----Original Message-----
>From:   Marc Rousseau [SMTP:mroussea@RICHTER.NET]
>Sent:   Monday, November 17, 1997 3:08 PM
>To:     Multiple recipients of list SQR-USERS
>Subject:        SQL UNION of two tables
>
>HI,
>
>Could somebody help me on this? I have to create a report to show the
>variance during a physical inventory. I have two tables, one with my
>frozen quantity for the products and another table with the quantity
>manually count.
>
>A product could be in both tables or in just one of the two. I want to
>read my two tables and print all the qty variance.
>
>Example:
>table 1 - product A with a qty of 10
>          - product B with a qty of 15
>          - product D qith a qty of 4
>
>table 2 - product A with a qty of 12
>           -product C with a qty of 6
>           -product D with a qty of 2
>           -product E with a qty of 20
>
>Result show be
>product A variance qty of 2
>product B variance qty of -10
>product C variance qty of 6
>product D variance qty of -2
>product E variance qty of 20
>
>I don't want to create a temporary table because i don't have the
>permission to do that. To complicate the situation i want to have it
>print from the biggest variance to the lowest one.
>
>Result show be
>product E variance qty of 20
>product C variance qty of 6
>product A variance qty of 2
>product D variance qty of -2
>product B variance qty of -10
>
>I know that the union could work in a begin-select statement but with
>some restrictions.
>
>Any ideas?
>
>Thanks
>
>Marc Rousseau
>Richter Systems International Inc.
>email: mroussea@richter.net
>phone: (514) 934-3400 ext: 4215
>
>Attachment Converted: C:\IM4\Re SQL UNION of two tables
>
Wm. G. Northen                  "Founded by Thomas Jefferson, 1819"
Programmer/Analyst              Unix, SQL, SQR, Sybase, Windows, ECT,
Development Group               Internet wgn3c@phil.dev.virginia.edu
University of Virginia          Tel (804) 924-3949