Alastair, Perhaps something like this would work?
SELECT codecolumn + FROM lookuptable t1 + WHERE NOT EXIST + (SELECT codeslistcolumn FROM datatable d2 + WHERE d2.codeslistcolumn CONTAINS t1.codecolumn) Bill On Tue, 9 Sep 2003 06:46:58 +0100, Alastair Burr wrote: >Thanks Bill, I will double check, but I need a NOT IN that looks at the >content of the field to find each of the two-character IDs rather one that >looks at the whole field. >Let me try and illustrate it quickly: >ID codes in rows in lookup table: >AA >AB >AC >BA >BB >BC >Data in rows in data column in data table: >AA,AB >AA,AB,AC >AA,AB,AC,AB >AA,BC >BA,BC >I want to find in the lookup table the row BB. It's easy enough to create a >temp table from the data table with a row for each code, delete the (many) >duplicates and then do the select. It was only because I could easily use >CONTAINS to find the codes which are in the data table that I jumped to the >conclusion that NOT CONTAINS would give me the missing ones. However, >because every code doesn't appear in at least one row they all, quite >correctly, get returned and, incidentally, it takes forever to do it.
