Need help with Tricky Query. - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Need help with Tricky Query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-12 : 11:32:15

[code]I need to JOINs 2 tables to get distinct values and insert into another table and can not figure out the best way how to do this.
or any other suggestions is greatly appreciate.

Thank you in advance.

Please see the desired output below.

--------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SC]') AND type in (N'U'))
DROP TABLE [dbo].[SC]
GO

CREATE TABLE [dbo].[SC]
(
[CustomerID] [varchar](5) NULL,
[ContactPhone1] [varchar](20) NULL,
[ContactFax1] [varchar](20) NULL,
[ContactEmail1] [varchar](25) NULL,
[ContactPhone2] [varchar](20) NULL,
[ContactFax2] [varchar](20) NULL,
[ContactEmail2] [varchar](25) NULL,
[ContactPhone3] [varchar](20) NULL,
[ContactFax3] [varchar](20) NULL,
[ContactEmail3] [varchar](25) NULL
) ON [PRIMARY]

GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[SC]([CustomerID], [ContactPhone1], [ContactFax1], [ContactEmail1], [ContactPhone2], [ContactFax2], [ContactEmail2], [ContactPhone3], [ContactFax3], [ContactEmail3])
SELECT N'BOA', N'2131234567', N'2131234765', N'contact1@boa.com', N'2131234678', N'2131234876', N'contact2@boa.com', N'2131234789', N'2131234987', N'contact3@boa.com' UNION ALL
SELECT N'KON', N'714617016', N'714617610', N'lmarin@kon.com', N'7146402578', N'7146402875', N'mbloch@kon.com', NULL, NULL, NULL
COMMIT;
RAISERROR (N'[dbo].[SC]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SELECT *
FROM SC;
GO

ContactPhone1 ContactFax1 ContactEmail1 ContactPhone2 ContactFax2 ContactEmail2 ContactPhone3 ContactFax3 ContactEmail3
------------- -------------------- ------------------------- -------------------- -------------------- ------------------------- -------------------- -------------------- -------------------------
BOA 2131234765 contact1@boa.com 2131234678 2131234876 contact2@boa.com 2131234789 2131234987 contact3@boa.com
KON 714617610 lmarin@kon.com 7146402578 7146402875 mbloch@kon.com NULL NULL NULL

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CusContact]') AND type in (N'U'))
DROP TABLE [dbo].[CusContact]
GO

CREATE TABLE [dbo].[CusContact]
(
[CustomerContactId] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] [char](3) NOT NULL,
[FirstName] [varchar](40) NOT NULL,
[LastName] [varchar](40) NOT NULL,
[Title] [varchar](30) NULL,
CONSTRAINT [PK_CustContact] PRIMARY KEY CLUSTERED
(
[CustomerContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[CusContact] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[CusContact]([CustomerContactId], [CustomerCode], [FirstName], [LastName], [Title])
SELECT 2, N'BOA', N'Lisa', N'Smith', N'President' UNION ALL
SELECT 3, N'BOA', N'Peter', N'Lee', N'CEO' UNION ALL
SELECT 4, N'BOA', N'John', N'Nguyen', N'Partner' UNION ALL
SELECT 5, N'KON', N'Marine', N'Lorenze', N'Asset Coordinator' UNION ALL
SELECT 6, N'KON', N'Bloch', N'Melissa', N'Asset Coordinator' UNION ALL
SELECT 7, N'KON', N'Andrew', N'Smith', N''
COMMIT;
RAISERROR (N'[dbo].[CustomerContact]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[CusContact] OFF;
GO

SELECT *
FROM CusContact
GO

CustomerContactId CustomerCode FirstName LastName Title
----------------- ------------ ---------------------------------------- ---------------------------------------- ------------------------------
2 BOA Lisa Smith President
3 BOA Peter Lee CEO
4 BOA John Nguyen Partner
5 KON Marine Lorenze Asset Coordinator
6 KON Bloch Melissa Asset Coordinator
7 KON Andrew Smith

SELECT *
FROM CusContact
GO

SELECT *
FROM SC;
GO

--Insert data into CustContactNumber

DROP TABLE [dbo].[CustContactNumber]
GO

CREATE TABLE [dbo].[CustContactNumber]
(
[CustomerCode] [char](3) NOT NULL,
[CustomerContactId] [int] NOT NULL,
[Phone] [varchar](20) NULL,
[Fax] [varchar](20) NULL,
[Email] [varchar](50) NULL

)
GO

Key: CustomerCode

ALTER TABLE dbo.CustContactNumber
ADD CONSTRAINT FK_CCN_CC
FOREIGN KEY (CustomerContactId) REFERENCES [CusContact] (CustomerContactId)
GO

----------------------------------------------------------------------------------------------------------------

-- The tricky part is how to get the correct CustomerContactId to insert into CustContactNumber table.

-- INSERT CustContactNumber (CustomerCode, CustomerContactId, CustomerContactId, Phone, Fax, Email)
SELECT DISTINCT
a.CustomerCode,
a.CustomerContactId,
c.Phone,
c.Fax,
CAST(c.Email AS VARCHAR(35)) AS 'Email'
FROM CusContact AS a
JOIN sc AS s
ON a.CustomerCode = s.CustomerID
CROSS APPLY ( VALUES ( ContactPhone1, ContactFax1, ContactEmail1 ),
( ContactPhone2, ContactFax2, ContactEmail2 ),
( ContactPhone3, ContactFax3, ContactEmail3 )
) AS c (Phone, Fax, Email )

WHERE NOT ( c.Phone IS NULL
AND c.Fax IS NULL
AND c.Email IS NULL );
GO

[code]-- Result want:

CustomerCode CustomerContactId Phone Cell Fax Email
------------ ----------------- -------------------- ---- -------------------- -----------------
BOA 2 2131234567 C 2131234765 contact1@boa.com
BOA 3 2131234678 C 2131234876 contact2@boa.com
BOA 4 2131234789 C 2131234987 contact3@boa.com

KON 5 714617016 C 714617610 lmarin@kon.com
KON 6 7146402578 C 7146402875 mbloch@kon.com[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:51:26

sorry can you explain how contact 3 repeated two times in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:06:55

still it persists? b/w where has 4 gone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-12 : 12:21:30

Sorry,  desire output should be:

-- Result want:

CustomerCode CustomerContactId Phone                Cell Fax                  Email
------------ ----------------- -------------------- ---- -------------------- -----------------
BOA          2                 2131234567           C    2131234765           contact1@boa.com
BOA          3                 2131234678           C    2131234876           contact2@boa.com
BOA          4                 2131234789           C    2131234987           contact3@boa.com

KON          5                 714617016            C    714617610            lmarin@kon.com
KON          6                 7146402578           C    7146402875           mbloch@kon.com


Thanks so much V.

quote:
Originally posted by visakh16

still it persists? b/w where has 4 gone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:30:44

[code]
SELECT t1.CustomerCode,t1.CustomerContactId,ContactPhone AS Phone,ContactFax AS Fax,ContactEmail AS Email
FROM (SELECT CustomerID,ContactPhone1 AS ContactPhone, ContactFax1 AS ContactFax, ContactEmail1 AS ContactEmail,1 AS Index
FROM SC
UNION ALL
SELECT CustomerID,ContactPhone2, ContactFax2 , ContactEmail3 ,2
FROM SC
UNION ALL
SELECT CustomerID,ContactPhone3, ContactFax2 , ContactEmail3 ,3
FROM SC)t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY CustomerCode ORDER BY CustomerContactId) AS rn,* FROM CusContact)t1
ON t1.CustomerCode = t.CustomerID
AND t1.rn = t.Index
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-12 : 12:50:31

Thank you very much Visakhm for your help.  I will test it out and learned what is in your codes.

Many thanks.

quote:

Originally posted by visakh16


SELECT t1.CustomerCode,t1.CustomerContactId,ContactPhone AS Phone,ContactFax AS Fax,ContactEmail AS Email
FROM (SELECT CustomerID,ContactPhone1 AS ContactPhone, ContactFax1 AS ContactFax,         ContactEmail1 AS ContactEmail,1 AS Index
      FROM SC
      UNION ALL
      SELECT CustomerID,ContactPhone2, ContactFax2 ,         ContactEmail3 ,2
      FROM SC
      UNION ALL
      SELECT CustomerID,ContactPhone3, ContactFax2 ,         ContactEmail3 ,3
      FROM SC)t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY CustomerCode ORDER BY CustomerContactId) AS rn,* FROM  CusContact)t1
ON t1.CustomerCode = t.CustomerID
AND t1.rn = t.Index

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:53:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources