Re: compound SQL queries and DISTINCT problem

Sten,

        I think the following will get the result you're looking for, but I'm not sure how efficient it is...

SELECT x FROM table1
WHERE table1.x NOT IN
(SELECT x FROM table2)
"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 Wed May 30 2001 - 15:53:27 CDT