SQL puzzle

Hi, I'm looking for some elegant SQL for the following scenario: 1-a very large table of data consisting of a key and code 2-there are approx. 100 codes 3-a case can consist of 1-n rows, where each row has a key and code 4-there are approx. 1 million cases I would like to count the cases that are missing one particular code, 'AT'. Thank you, Tony Demeis Database Administrator Ministry of Health and Long-Term Care Tel: 416-327-7718

Demeis, Tony wrote: > Hi, > > I'm looking for some elegant SQL for the following scenario: > > 1-a very large table of data consisting of a key and code > 2-there are approx. 100 codes > 3-a case can consist of 1-n rows, where each row has a key and code > 4-there are approx. 1 million cases > > I would like to count the cases that are missing one particular code, 'AT'. Would something like this get you what you want? (I created a couple of small tables for testing purposes.) My test returned the correct count results... create table cases (keyval smallint,codeval char(2)); insert into cases values (1, "AA"); insert into cases values (2, "AA"); insert into cases values (3, "AA"); insert into cases values (1, "AT"); insert into cases values (1, "AU"); insert into cases values (2, "AU"); insert into cases values (3, "AU"); create table worktemp (keyval smallint,codeval char(2)); insert into worktemp select unique keyval, "AT" from cases; select count(*) from worktemp where keyval NOT IN (select keyval from cases where codeval = "AT"); drop table worktemp; drop table cases; -- June Hunt