"Leo Violette" <vitel30@yahoo.com> wrote in message
news:ukUXecGbDHA.2928@tk2msftngp13.phx.gbl...
> I'm using MS Sql Server MSDE
>
> Table1
> shotID NextShotID CaromShotID
> 1 2 3
> 2 NULL NULL
> 3 NULL NULL
>
> I want to select the row where NextShotID is null and shotID does not
exist
> in the CaromShotID column.
>
> Since ShotID1 contains a non-null shotID2, it should be omitted.
> Since ShotID3 is found in the CaromShotID column for ShotID1, it shoud be
> omitted.
> This should leave ShotID2.
>
> I've read that using Not in here with columns that return NULL is a
problem.
> How would I rewrite?
>
> My current attempt returns no rows.
>
> I try:
> select *
> From Table1
> Where CaromShotID not in
> (
> Select ShotID
> From Table1
> Where NextShotID IS NULL
> )
>
>
> Hope this makes sense.
>
> microsoft.public.sqlserver.programming
>
>
>
>
>

"oj" <nospam_ojngo@home.com> wrote in message
news:enW0ogGbDHA.2016@TK2MSFTNGP10.phx.gbl...
> select *
> From Table1 t1
> Where not exists
> (
> Select *
> From Table1 t2
> Where NextShotID IS NULL
> and t2.ShotID = t1.CaromShotID
> )
>
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
>
>
> "Leo Violette" <vitel30@yahoo.com> wrote in message
> news:ukUXecGbDHA.2928@tk2msftngp13.phx.gbl...
> > I'm using MS Sql Server MSDE
> >
> > Table1
> > shotID NextShotID CaromShotID
> > 1 2 3
> > 2 NULL NULL
> > 3 NULL NULL
> >
> > I want to select the row where NextShotID is null and shotID does not
> exist
> > in the CaromShotID column.
> >
> > Since ShotID1 contains a non-null shotID2, it should be omitted.
> > Since ShotID3 is found in the CaromShotID column for ShotID1, it shoud
be
> > omitted.
> > This should leave ShotID2.
> >
> > I've read that using Not in here with columns that return NULL is a
> problem.
> > How would I rewrite?
> >
> > My current attempt returns no rows.
> >
> > I try:
> > select *
> > From Table1
> > Where CaromShotID not in
> > (
> > Select ShotID
> > From Table1
> > Where NextShotID IS NULL
> > )
> >
> >
> > Hope this makes sense.
> >
> > microsoft.public.sqlserver.programming
> >
> >
> >
> >
> >
>
>