"Meher Malakapalli" <mehersql@yahoo.com> wrote in message
news:%230Ue%23WBrDHA.2688@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I need some help in deleting associated records in two tables. My table
DDL
> is as follows:
>
> Create table tblPSO (Rowid int identity (1,1),
> SiteID int,
> SCN int)
>
> Create table tblPSOSite (Rowid int identity(1,1),
> SiteID int,
> Cmpid int)
>
> Insert into tblPSO(SiteID,SCN) Values (23, 56533)
> Insert into tblPSO Values (25, 56533)
> Insert into tblPSO(SiteID,SCN) Values (26, 56433)
>
>
> Insert into tblPSOSite(SiteID, Cmpid) Values (23, 457)
> Insert into tblPSOSite (SiteID, Cmpid) Values (25, 459)
> Insert into tblPSOSite (SiteID, Cmpid) Values (26, 478)
>
> What I need to do is identify the different CID values in the table
> tblPSOSite for the same SCN in the other table (tblPSO) and delete the
> records. In other words the two different Cmpid values un the tblPSOSite
are
> 457 and 459 for the Same ICN 56533 in the other table tblPSO. The tblPSO
and
> tblPSoSite can be joined on SiteID. I need to delete the two record in the
> tblPSOSite with the values 457 and 459.
>
> I tried to achieve by joining the two tables on the siteID and then used a
> self join on tblPSOSite but could not achieve it. Any help is appreciated
if
> there is a easier query to achieve what I want.
>
> Thanks
>
> M
>
>
>
>
>
>

>> I need some help in deleting associated records [sic] in two tables.

>> In other words the two different cmp_id values in the PSOsites are

"Joe Celko" <joe.celko@northface.edu> wrote in message
news:OgytNiUrDHA.2536@tk2msftngp13.phx.gbl...
> >> I need some help in deleting associated records [sic] in two tables.
> My table DDL is as follows: <<
>
> No, you need help learning SQL. Rows are not records. In SQL the terms
> for related tables are "referenced table" and "referencing table" and
> the relationship is usually enforced by DRI. I think that the term
> "associated records" was part of the old IDMS system, but I could be
> wrong.
>
> Tables require a key (I hope you know that an IDENTITY is never a key by
> definition). This schema looks like a bad file system, without any data
> integrity.
>
> Also, stop putting that silly, redundant "tbl-" prefix on table names --
> you look like you are still writing BASIC or Fortran II.
>
> Guessing at a valid, normalized schema, with minimal integrity
> constraints, is this what you meant to post?
>
> CREATE TABLE PSO
> (site_id INTEGER NOT NULL PRIMARY KEY,
> scn INTEGER NOT NULL);
>
> CREATE TABLE PSOsites
> (site_id INTEGER NOT NULL UNIQUE,
> cmp_id INTEGER NOT NULL,
> PRIMARY KEY (site_id, cmp_id));
>
> INSERT INTO PSO VALUES (23, 56533);
> INSERT INTO PSO VALUES (25, 56533);
> INSERT INTO PSO VALUES (26, 56433);
>
> INSERT INTO PSOsites VALUES (23, 457);
> INSERT INTO PSOsites VALUES (25, 459);
> INSERT INTO PSOsites VALUES (26, 478);
>
> >> In other words the two different cmp_id values in the PSOsites are
> 457 and 459 for the same icn 56533 in the table PSO. The PSO and
> PSOsites can be joined on site_id. I need to delete the two records
> [sic] in the PSOsites with the values 457 and 459. <<
>
> I think that you meant that if a site_id has two or more scn values,
> then you delete it from PSOsites. Otherwise, you keep the singletons.
>
> DELETE FROM PSOsites
> WHERE site_id
> IN (SELECT P1.site_id
> FROM PSO AS P1
> WHERE 1 <
> (SELECT COUNT(*)
> FROM PSO AS P2
> WHERE P2.scn = P1.scn));
>
> I have the feeling that with a better schema design, this would either
> be handled by DRI or by a single table and never need a DELETE FROM to
> clean it up.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

>> As for putting the Key this table was basically designed by someone

"Joe Celko" <joe.celko@northface.edu> wrote in message
news:%23lTQHPVrDHA.2616@TK2MSFTNGP09.phx.gbl...
> >> As for putting the Key this table was basically designed by someone
> else and also the data we get is bad. Personally I would hate to write
> code to clean up data but I have no choice. <<
>
> The fifth labor of Hercules was to clean the stables of King Augeas in a
> single day. The Augean stables held thousands of animals and were over a
> mile long. This story has a happy ending for two reasons: (1) Hercules
> got one tenth of the cattle for his work (2) At the end of the story of
> the Labors of Hercules, he got to kill the bastard that set him for this
> job with his bare hands.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

>> Thanks Joe. Yeah you are right. If I do the clean up now I will be

>> there is a book that you have written about Database design and