Re: compound SQL queries and DISTINCT problem

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

>