Re: A difficult query

I believe you need an outer join (indicated by adding '(+)' to your join condition)

Select id
From A

         ,B
Where A.id(+) = B.id
And B.mark != 'X';

regards,
Ievell

"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 Sun Sep 08 2002 - 16:42:50 CEST