Re: compound SQL queries and DISTINCT problem

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 Thu May 31 2001 - 05:26:17 CDT