Re: existence question

"Jeff Kish" <jeff.kish_at_mro.com> schrieb im Newsbeitrag news:km3tf0l093830meughmcmocfftgd425u4t_at_4ax.com...
> 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.

okay, the following does what you want. I tried it out. there was a mix-up with the c3 and c5 before. There are two statements because i am still not sure if you want the "and" or "or" flavour regarding the NOT NULL clauses. In your examples above they give identical results...

select * from <blah> b1, <blah> b2
where b1.c1 = b2.c1 and b1.c2 = b2.c2 and b1.c3 = b2.c3 and b1.c4 is null and b1.c5 is null
and b2.c5 is not null and b2.c5 is not null

or

select * from <blah> b1, <blah> b2
where b1.c1 = b2.c1 and b1.c2 = b2.c2 and b1.c3 = b2.c3 and b1.c4 is null and b1.c5 is null
and (b2.c5 is not null or b2.c5 is not null)

>
> Maybe I need "group" somewhere, I don't know.
> Thanks
> Jeff Kish
Received on Thu Jul 22 2004 - 04:44:25 CDT