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.
It's a bit like using an automatically repeating SSUB where the substring number increments by one automatically for each possible ID code, knows how many times to do it for each row and only reports the ones it doesn't find - not much to ask! 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. Regards, Alastair. ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:19 PM Subject: [RBASE-L] - Re: Finding unused id codes > Alastair, > > SELECT codecolumn FROM lookuptable + > WHERE codecolumn NOT IN + > (SELECT codecolumn FROM ForeignKeyTable) > > Bill > > > > On Mon, 8 Sep 2003 23:02:27 +0100, Alastair Burr wrote: > > >Hello everyone, > > >I'm trying to find what codes from a look-up table are not currently in use: > > >I have a data table that has, amongst other columns, a text column with a > >(variable) number of 2 character codes separated by a comma, viz: > >aa,ab,ac,ba,bb,bc... > > >The look-up table has code column with a row for each code and a > column with > >a description. > > >I can find which codes are in use easily enough and I thought that the > >missing ones could be found by "reversing" the logic but it doesn't work: > > >Briefly, SELECT... WHERE ... CONTAINS tell me the ones in use but > SELECT... > >WHERE ... NOT CONTAINS gives me everything. > > >I can think of a way to do it using a temp table but I wondered if there was > >an SQL way to find the unused items? Anybody got any ideas? > > >Thanks in advance, > >Regards, > >Alastair. > > >---------------------------------- > >A D B Burr, > >St. Albans, UK. > >---------------------------------- > >[EMAIL PROTECTED] > >---------------------------------- >
