INSERT statement to add to list; DDL included

Question

INSERT statement to add to list; DDL included

emily-1119612

SSCarpal Tunnel

Points: 4166
More actions
August 31, 2009 at 9:31 am

#97985

Any help is appreciated.
I have Products, Accounts, and ProductAccountLists. Products have a manufaturer.
I am looking for an insert statement that will insert into ProductAccountLists according to the following logic.
If a particular account has one product from a manufacturer I want to add all of the other products from that manufactuer to that particular accounts list.
Given my sample data I would want to insert these records to table ProductAccountLists
A,2
A,3
C,4
CREATE TABLE #Products
(
ProductID int,
Manufacturer varchar(20)
)
INSERT INTO #Products
SELECT '1', 'Company ABC' UNION
SELECT '2', 'Company ABC'UNION
SELECT '3', 'Company 1'UNION
SELECT '4', 'Company 1'UNION
SELECT '5', 'Company ZXY'
SELECT * FROM #Products
CREATE TABLE #Accounts
(
Account varchar(1)
)
INSERT INTO #Accounts
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C'
SELECT * FROM #Accounts
CREATE TABLE #AccountProductLists
(
Account varchar(1),
ProductID int
)
INSERT INTO #AccountProductLists
SELECT 'A',1 UNION
SELECT 'A',4 UNION
SELECT 'B',5 UNION
SELECT 'C',3
SELECT * FROM #AccountProductLists
DROP TABLE #Products
DROP TABLE #Accounts
DROP TABLE #AccountProductLists

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]There is a bit confusion between the requirement and table definitions given by you. There is not any relationship between the tables you have specified. Please post with atleast exact table definition and with the exact o/p.

Mahesh[/font]

MH-09-AM-8694

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

If I understood correctly your requirement, this is one way of doing so. I did 2 joins to table #products. The first one is to get the product’s manufacturer. The second join to #products get all the manufacturer’s products except for the products that the account already has. There is a good chance that you'll get other ways to do it.

insert into #AccountProductLists (Account, ProductID)

select AP.Account, P2.ProductID

from #AccountProductLists AP INNER JOIN #Products P ON AP.ProductID = P.ProductID

INNER JOIN #Products P2 ON P.Manufacturer = P2.Manufacturer AND P2.ProductID NOT IN (

select ProductID from #AccountProductLists APL WHERE APL.Account = AP.Account)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 3

Adi Cohn (9/1/2009)

If I understood correctly your requirement, this is one way of doing so. I did 2 joins to table #products. The first one is to get the product’s manufacturer. The second join to #products get all the manufacturer’s products except for the products that the account already has. There is a good chance that you'll get other ways to do it.

Yes this works, thank you.

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 4

Mahesh Bote (9/1/2009)

[font="Verdana"]There is a bit confusion between the requirement and table definitions given by you. There is not any relationship between the tables you have specified. Please post with atleast exact table definition and with the exact o/p.

Thanks for replying. I'll try to be clearer in the future and post relationships. What is 'o/p'?