Re: A difficult query

Hi,
it seems that suggested solution doesn't work, so you can try with this: SELECT a.id

  FROM A , B WHERE a.ID = b.id(+)

     AND b.mark IS NULL;

I thinx that first solution u got is wrong.

the best regards

mark

"Conny Roloff" <connyroloff_at_gmx.de> wrote in message news:f81368f0.0209071543.734e1f2f_at_posting.google.com...
> Hi, NG,
>
> I can't find a fine solution for my problem:
>
> From two tables I need similar colums. From table A I need all rows
> except from those, which are marked in table B. From table B I need
> all rows except from those, which are not in table A. Here is an
> example:
>
> Rows of table A (id):
> 1
> 2
> 3
> 4
>
> Rows of table B (id, mark):
> 2,
> 3,
> 4,X <-- not wanted, because it's marked
> 5, <-- not wanted, because it's not in table A
>
> Needed result:
> 1
> 2
> 3
>
> I ended up in this:
>
> select id from A where not exists
> (select * from B where A.id = B.id and mark='X')
> union
> select id from B where exists
> (select * from A where A.id = B.id)
>
> But this seems to me a bit complicated. In reality both main selects
> are very complex, so that I get a huge resulting SQL-statement which
> is probably not quite performant (In fact I generate it by java).
>
> Does somebody know a better solution?
>
> Thx in advance for each little hint.
>
> Conny
Received on Mon Sep 09 2002 - 20:41:28 CEST