Select Unique and Common rows from two tables

Hi,

I have a SQL problem that I've been struggling with for the last several days, I was hoping that someone could lend me a hand. I have two tables that I need to read and build an output set based upon what is in them. If a row is unique to table A I want it as part of my output set. If the key in table A is the same as the key in table B (PONUM & POLINENUM), but the rest of the fields are different then I want the key followed by table A's numeric fields less table B's in the output set. There never should be unique rows in Table B, if there are I don't want to know about them.

Ideally I would love to be able to do this in a single select statement, but I haven't been able to figure that out. I thought about building a view (superset) of both tables and then doing a select that would do the subtraction for me, but I couldn't figure out how to get both tables rows into the same view.

Can anyone give me a hand? Thanks in advance,

C. Duncan Hudson

Table A looks like this:

PONUM POLINENUM QUANTITY LINECOST
-------- --------- --------- ---------

KW1161           1         2        20
KW1161           1         8        80
KW1162           3         5       250
KW1162           2         4     81.96
KW1162           1         1     72.36
DUNC-1           1        10     100.1
DUNC-2           1        10        10

Table B looks like this:

PONUM POLINENUM INVOICEQTY LINECOST -------- --------- ---------- ---------

KW1161           1          2        20
KW1161           1          3        30
KW1161           1          5        50
KW1162           1          1     72.36
KW1162           2          4     81.96
DUNC-1           1          5      50.5

The output set should look like this:

PONUM POLINENUM QUANTITY LINECOST
-------- --------- --------- ---------

KW1162           3         5       250    <- was unique to A
DUNC-1           1         5      50.5    <- A's row minus B's row
DUNC-2           1        10        10    <- was unique to A

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 18 1998 - 10:40:06 CDT