Re: NOT EXIST clause

In <B3ZWUSLS36M*/R=ESVAX/R=A1/U=FARROWDW/@MHS>, Daniel W Farrow IV 89-28759 fax 99-26758 wrote: >Try NOT IN with a common field. Your common field should be indexed. > > select * from test:a where commonfield not in > (select commonfield from live:b)> >I do this all the time and it works for me. > >Be seeing you, >-DAN > >>I am using the NOT EXIST clause in a select statement and it appears >to >>not be working. I know that there is one record in table a that is >not >>in table b and I though that it would give me that one record. >> >> SQL >> >> select * from test:a where NOT EXIST >> (select * from live:b)>> >>It returns with no records. I have noticed that this statement works >on >>a table with less records in it. Does someone have a solution. > This solution will not work if there is a null value in commonfiled in any row of table b. Try the following : select * from test:a where commonfield not in (select commonfield from live:b where commonfield is not null) or even select * from test:a where commonfield not in (select commonfield from live:b where length(commonfield) > 0 ) This is certainly the case under SE 5 anyway - maybe they fixed it for online. Regards, Sean Birkhead Information Solutions (Ire.) Ltd.