Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Union query
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.
Here's one possibility. Certainly better solutions may be possible depending on the actual queries involved.
WITH t AS
(SELECT f1,f2 FROM tbl1 /* query 1 */)
SELECT f1,f2 FROM t
UNION
SELECT tbl2.f1, tbl2.f2
FROM tbl2 /* query 2 */, t
WHERE tbl2.f1 = t.f1 ;
-- David PortasReceived on Tue Dec 12 2006 - 05:30:26 CST