Re: Union query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Dec 2006 03:29:04 -0800
Message-ID: <1165922944.082122.118940@j44g2000cwa.googlegroups.com>

Abu Hamza wrote:
> I have a query of the form
>
> query1 union query2
>
> Both queries have a common field f1.
> My problem is to get only those records from query2 which have a
> corresponding record (same f1 value) in query1. In other words there
> should be no record in query2 that has x in f1 and there does not exist
> an x in query1. Is there a simple way or do I have to write a complex
> 'exists' condition in query2 ?
>
> thx.

Please post the SQL statements that you are working with, and the expected output for assistance.

A very basic example:
The set up:
CREATE TABLE TABLE1 (X NUMBER(10), Y NUMBER(10), Z NUMBER(10)); CREATE TABLE TABLE2 (X NUMBER(10), Y NUMBER(10), Z NUMBER(10));

INSERT INTO TABLE1 VALUES (1,1,5);
INSERT INTO TABLE1 VALUES (1,3,5);
INSERT INTO TABLE1 VALUES (2,6,1);
INSERT INTO TABLE1 VALUES (5,9,8);

INSERT INTO TABLE2 VALUES (2,1,100);

INSERT INTO TABLE2 VALUES (5,1,150);
INSERT INTO TABLE2 VALUES (12,5,50); SELECT
  X,
  Y,
  Z
FROM
  TABLE1
UNION ALL
SELECT
  X,
  Y,
  Z
FROM
  TABLE2;          X Y Z
========== ========== ==========

	 1	    1	       5
	 1	    3	       5
	 2	    6	       1
	 5	    9	       8
	 2	    1	     100
	 5	    1	     150
	12	    5	      50

The row from TABLE2 containing 12 in column X should not be returned, because there are no rows in TABLE1 containing 12 in column X.

Modification of the above query:
SELECT
  X,
  Y,
  Z
FROM
  TABLE1
UNION ALL
SELECT

  T2.X,
  T2.Y,
  T2.Z

FROM
  TABLE2 T2,
  (SELECT DISTINCT
    X
  FROM
    TABLE1) T1
WHERE
  T1.X=T2.X;

Note that we are now retrieving all of the distinct values of X in TABLE1 within an inline view, and joining that to the column X in TABLE2.
         X Y Z
========== ========== ==========

	 1	    1	       5
	 1	    3	       5
	 2	    6	       1
	 5	    9	       8
	 2	    1	     100
	 5	    1	     150

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Dec 12 2006 - 05:29:04 CST