A difficult query
Date: 7 Sep 2002 16:43:10 -0700
Message-ID: <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):
Rows of table B (id, mark):
Needed result:
I ended up in this:
select id from A where not exists
(select * from B where A.id = B.id and mark='X')
(select * from A where A.id = B.id)
1
2
3
4
2,
3,
4,X <-- not wanted, because it's marked
5, <-- not wanted, because it's not in table A
1
2
3
union
select id from B where exists
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 - 01:43:10 CEST