Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: existence question
On Wed, 21 Jul 2004 17:13:48 +0200, "André Hartmann"
<andrehartmann_at_hotmail.com> wrote:
>
<snip>
>
>No, if you dont want to stuff in any :1 or :2 then just make sure you join
>properly:
>
>select * from <blah> b1, <blah> b2
>where b1.c1 = b2.c1 and b1.c2 = b2.c2
>and b1.c3 is null and b1.c4 is null
>and b2.c3 is not null and b2.c4 is not null
>
>After thinking it over again I am not quite sure about how you want to "NOT
>NULL"s to be evaluated. you might actually want "... and (b2.c3 is not null
>or b2.c4 is not null)" as the last line in the above statement... try it out
>:)
>
>>
>> Regards,
>> Jeff Kish
>
Thanks. I don't seem to be getting what I think I should. I tried the query
and got no rows back. (I hope to heaven I'm not being incredibly dumb here)
I may have misstated the question slightly in terms of c1 etc...I forgot c3 which is probably not significant in figuring out the solution.
If you can persevere..Here is some data: This is OK per business rule,
c1 c2 c3 c4 c5 1001 smith smith NEWYORK 1001 smith smith SOMETHING 1002 jones jones NEWYORK 1002 jones jones SOMETHING
This is not OK per business rule because a c1c2c3 has more than one row when there exists a row with c4 and c5 as null
c1 c2 c3 c4 c5 1001 smith smith NEWYORK 1001 smith smith This is not OK (twice bad) per business rule c1 c2 c3 c4 c5 1001 smith smith NEWYORK 1001 smith smith 1002 jones jones NEWYORK 1002 jones jones
If I can come up with a where clause that tells me if either of the second conditions or similar ones exist it would meet my goal I think, maybe by not being an empty set or something.
Maybe I need "group" somewhere, I don't know.
Thanks
Jeff Kish
Received on Wed Jul 21 2004 - 10:48:11 CDT