[dba-SQLServer] Isolating matching and related records

Hi Mark:

That looks like the solution. 

Brilliant, Thanks you very much.

Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark A
Matte
Sent: Friday, July 06, 2007 1:16 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Isolating matching and related records

Sorry Jim,

Forgot to include SQL...sent to accessD also...Here is the single query 
approach...

**************SQL***************
DELETE tblTest.Section, tblTest.code
FROM tblTest
WHERE (((tblTest.Section)="C1") AND ((tblTest.code) Not In (SELECT 
tblTest1.code
FROM tblTest AS tblTest1
GROUP BY tblTest1.Section, tblTest1.code
HAVING (((tblTest1.Section)="B1"));)));
**************SQL***************

>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Isolating matching and related records
>Date: Fri, 06 Jul 2007 12:48:36 +0000
>
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
http://im.live.com/messenger/im/home/?source=hmtextlinkjuly07

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com