Re: Select Unique and Common rows from two tables

From: replace this with _at_ <_at_)xs4all.nl>
Date: Fri, 18 Sep 1998 21:55:51 GMT
Message-ID: <3602cc70.9023324@news.xs4all.nl>

On Fri, 18 Sep 1998 15:40:06 GMT, c_duncan_hudson_at_yahoo.com wrote:

>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.

Maybe you can improve a lot by rewriting the following, but at least it works (in one statement):

SELECT  A.PONUM
,       A.POLINENUM
,       A.QUANTITY - DECODE(B.INVOICEQTY,NULL,0,B.INVOICEQTY)
,       A.LINECOST - DECODE(B.LINECOST, NULL,0,B.LINECOST)

FROM A,B
WHERE (A.PONUM, A.POLINENUM) IN
   (SELECT AA.PONUM, AA.POLINENUM
    FROM A AA

	GROUP BY AA.PONUM, AA.POLINENUM    
	HAVING COUNT(*) = 1    

   )
AND NOT EXISTS
   (SELECT BB.PONUM
    FROM B BB

	WHERE  BB.PONUM = A.PONUM
	AND    BB.POLINENUM = A.POLINENUM
	AND    BB.INVOICEQTY = A.QUANTITY
	AND    BB.LINECOST = A.LINECOST
   )
AND A.PONUM     = B.PONUM (+)  

AND A.POLINENUM = B.POLINENUM (+)
AND (A.QUANTITY <> B.INVOICEQTY (+))

---

• Martin Jesterhoudt
• martinj(replace this by @)xs4all.nl

  ---

Received on Fri Sep 18 1998 - 16:55:51 CDT