Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 add and remove items without affecting other items

Author  Topic 

nic
Posting Yak Master

209 Posts

Posted - 2004-04-16 : 11:00:07
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

nic
Posting Yak Master

209 Posts

Posted - 2004-04-16 : 14:53:54
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-16 : 15:08:51
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...

Go to Top of Page

nic
Posting Yak Master

209 Posts

Posted - 2004-04-16 : 15:34:14
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
   

- Advertisement -