Re: compound SQL queries and DISTINCT problem

then you want to use NOT EXISTS instead
of the set operators, something like this:

select x
  from table1 t1
 where not exists ( select 1

                      from table2 t2
                     where t1.x = t2.x )

HTH "Sten Loecher" <sl13_at_inf.tu-dresden.de> wrote in message news:9f56b5$knj$1_at_kastor.inf.tu-dresden.de...
> x
> -
> D
>
> is the result set I want. However, the solution from Abey will work.
>
> Thank you fellows for your help.
>
> "Spencer" <spencerp_at_swbell.net> schrieb im Newsbeitrag
> news:Y%gR6.129$fw1.175910_at_nnrp2.sbc.net...
> > just so i understand what result set you want, what if
> >
> > select x from table2
> >
> > x
> > -
> > A
> > B
> > C
> >
> > would you want
> >
> > x
> > -
> > B
> > D
> >
> > or
> >
> > x
> > -
> > D
> >
> >
> > ?
> >
> > "Sten Loecher" <sten-loecher_at_gmx.de> wrote in message
> > news:9f2pbv$br$1_at_kastor.inf.tu-dresden.de...
> > > Hello Stephen,
> > >
> > > thank you for your quick response. Unfortunatly, you did not
 understand
 me
> > > properly ... my fault. Let me show an example:
> > > consider these two queries and their result:
> > >
> > > select x from table1
> > >
> > > x
> > > ---
> > > A
> > > B
> > > B
> > > C
> > > D
> > >
> > > select x from table2
> > >
> > > x
> > > ---
> > > A
> > > C
> > >
> > > If you use the minus operator in the following way:
> > >
> > > select x from table1
> > > minus
> > > select x from table2
> > >
> > > you will get the result
> > >
> > > x
> > > ---
> > > B
> > > D
> > >
> > > despite of the fact, that table1 contains the row B two times. I'd
 like
 to
> > > get the result:
> > >
> > > x
> > > ---
> > > B
> > > B
> > > D
> > >
> > >
> > > Any ideas ? Would be great.
> > >
> > > Sten
> > >
> > > Stephen Bell <stephen.bell_at_cgi.ca> schrieb in im Newsbeitrag:
> > > 3B14E0C4.71EA098B_at_cgi.ca...
> > > > Hi Sten,
> > > > I'm not clear on exactly what you want..if you want the rows from
 BOTH
 tables
> > > > you can use UNION or UNION ALL..
> > > >
> > > > Steve
> > > >
> > > > Sten Loecher wrote:
> > > >
> > > > > Hi folks,
> > > > >
> > > > > I need to write compound queries like this:
> > > > >
> > > > > select * from some_table1
> > > > > minus
> > > > > select * from some_table2
> > > > >
> > > > > or:
> > > > >
> > > > > select * from some_table1
> > > > > intersect
> > > > > select * from some_table2
> > > > >
> > > > > The problem is, that I need all rows from some_table1 or
 some_table2
> > > > > respectivly. However, the minus and intersect operator returns
 only
 distinct
> > > > > rows from the according tables. The SQL standard allows for
 example
> > > > > INTERSECT ALL. Oracle do not provide this feature. Does anyone
 have
 an
 idea
> > > > > to get all rows without using predicates in the where clause of
 the
> > > > > statements ?
> > > > >
> > > > > Would appreciate any help.
> > > > >
> > > > > Sten
> > > >
> > >
> > >
> > >
> >
> >
>
>
Received on Fri Jun 01 2001 - 22:09:05 CDT