[dba-SQLServer] Isolating matching and related records

Jim,

Wrote in access...syntax should be similar.  I used a sub query to find ALL 
Codes for Section B1...then deleted all records where section was C1 and 
codes where NOT in my subquery.

Hope it helps,

Mark A. Matte


>From: Jim Lawrence <jlawrenc1 at shaw.ca>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: [dba-SQLServer] Isolating matching and related records
>Date: Thu, 05 Jul 2007 20:20:59 -0700
>
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
Local listings, incredible imagery, and driving directions - all in one 
place! http://maps.live.com/?wip=69&FORM=MGAC01