SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 add and remove items without affecting other items
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nic
Posting Yak Master

209 Posts

Posted - 04/16/2004 :  11:00:07  Show Profile  Reply with Quote
Hi,
We have a management system that tracks information about different companies for various products. A specific account might consist of a number of different companies. The detail information about each company can be retrieved from the CompanyProductDetails table. If you load the below data, you will see that account '1' is associated with product 'A'. For product 'A', company '1' and '2' have various detail records.

This is the problem. For an account, the product might change. If this occurs, I need to do perform two steps. first delete all the details records from AccountDetails for the old product that doesn't also exist for the new product. Second, after deleteing the old records, I need to insert into AccountDetails all the new details that don't currently exist. The main thing is the items that are incommon between products A and B must remain in the account details table. I just can't delete all the records and insert new ones (in reality, there are child tables associated with the detail so I can't loose the data).

In the attached example, if switching from product A to B:
Step 1
for company1 delete detail 2 and 3 from AccountDetails
for company2 delete detail 2 and 3 from AccountDetails

Step 2
for company1 insert detail 4 and 5 into AccountDetails
for company2 insert detail 5 into AccountDetails

To make a long question short, detail 1 is shared between productA and B so must be left alone .

I am stumped on how the sql queries necessary do this. any help would be appreciated.


CREATE TABLE CompanyProductDetails (
	[CompanyID] [varchar] (10),
	[Product] [varchar] (10),
	[DetailItem] [varchar] (10)
)
GO

CREATE TABLE AccountProducts (
	[AccountID] [varchar] (10),
	[Product] [varchar] (10) 
)
GO

CREATE TABLE AccountDetails (
	[AccountID] [varchar] (10),
	[CompanyID] [varchar] (10),
	[DetailItem] [varchar] (10)
)
GO

INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail1') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail2') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail3') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail1') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail2') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail3') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail1') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail4') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail5') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodB','detail1') 
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodB','detail5') 
GO

INSERT INTO AccountProducts VALUES('acc1','prodA') 
GO

INSERT INTO AccountDetails VALUES ('acc1','comp1','detail1') 
GO
INSERT INTO AccountDetails VALUES ('acc1','comp1','detail2') 
GO
INSERT INTO AccountDetails VALUES ('acc1','comp1','detail3') 
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail1') 
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail2') 
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail3') 
GO




Nic

Edited by - nic on 04/16/2004 11:35:19

nic
Posting Yak Master

209 Posts

Posted - 04/16/2004 :  14:53:54  Show Profile  Reply with Quote
geesh, either my question made no sense (most likely) or it is somewhat tricky. Anyway, I think I might have figured it out. First, in a derived table, I get everything the two accounts have in common, I then do a join to the accountDetails table and delete everything that the account DON'T share. I haven't really done too much with joining using <> instead of =. Does this look sound? I essentially want to delete everything from account detail record that is not in the derived table. Is this the best way to do that?

Thanks


DELETE AccountDetails
FROM
	AccountDetails a INNER JOIN 
	(
	-- get everything the old and new set share
	SELECT 
		a.companyID
		,a.detailItem
	FROM
		CompanyProductDetails a INNER JOIN CompanyProductDetails b 
                ON a.CompanyID = b.CompanyID AND a.DetailItem = b.DetailItem
	WHERE
		a.Product = 'prodA'
		and b.Product = 'prodB'
	) AS DT1 ON a.CompanyID <>  DT1.CompanyID AND a.detailItem <> DT1.detailItem
WHERE
       a.AccountID = 'acc1'


Nic

Edited by - nic on 04/16/2004 14:56:01
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/16/2004 :  15:08:51  Show Profile  Reply with Quote
Well I guess that what got me...

Normalized data doesn't "Share" attributes (in this case children rows) between Entities...

So yes, I'm confused...



Brett

8-)

EDIT: Even DeNormalized data wouldn't do this...


Edited by - X002548 on 04/16/2004 15:09:26
Go to Top of Page

nic
Posting Yak Master

209 Posts

Posted - 04/16/2004 :  15:34:14  Show Profile  Reply with Quote
I guess my naming conventions weren't very clear. I tried to use general names as to make it more understandable. In reality all the tables relate to homeInsurance. One insurance quote may consist of one or more different companies. All of these companies may or may not have certain endorsements (credits). The endorsements are standardized. So if company A and B both have endorsement 1, the endorsement data is the same. I needed to find a way to keep valid endorsements when other items in the quote (which affect endorsements) are changed.

Anyway, thanks for everyone's help.

Nic
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000