[dba-SQLServer] Isolating matching and related records

Jim Lawrence jlawrenc1 at shaw.ca
Thu Jul 5 22:20:59 CDT 2007


Hi All:

The following question seems like a simple SQL question but I just can not
find the appropriate answer at this minute... too many long nights.

Given: 

1. A table of many records, with each record having a couple fields called
{section] and [code]. 

2. The content of field [section] has only three possible entries of
"A1","B1" and "C1". The contents of field [code] can be any of a variety of
codes.
   
3. There will always be records for group "A1" and "C1" but not always for
"B1"

What SQL code would find the group where there are no matching "B1" records
and delete all the records in the "C1" group 

An example of data could be;

5 records of [section] = "A1", [code] = "TTR"
4 records of [section] = "B1", [code] = "TTR"
30 records of [section] = "C1", [code] = "TTR"
3 records of [section] = "A1", [code] = "CYR"
47 records of [section] = "C1", [code] = "CYR"
5 records of [section] = "A1", [code] = "PIJ"
4 records of [section] = "B1", [code] = "PIJ"
30 records of [section] = "C1", [code] = "PIJ"

After the SQL code was run all 47 records with [section] = "C1' and [code] =
"CYR" would be gone.

It does not seem like a difficult process but...

MTIA

Jim




More information about the dba-SQLServer mailing list