One subscriver, multiple phone numbers.

[sylvaincyr]

Hi,

I dont know if it's gonna be as tough for you guys as it is for me to find the right solution.

Context :

I have 3 tables, abonnement (means subscriber), LnkComClient (which contains the communication values associated with abonnement) and of course
TypeCom (which represents the types of communication : email, fax, cellphone, toll-free number)

I designed my tables this way so I can have as many type of communication possible as I want and also be ready if new ones appear in the future and for each of these type I could have as many entries as I want. Let's say I could have 5 email adresses,3 phones numbers, 3 website urls, etc... all that for 1 abonnement (subscriber).

Here is my current query which is fast but return only the max value for each type of communication which is totally against my design. The query itself is not enough dynamic, if new type of communication appears, it fails to achieve what I want but for dynamic side but it's not a show stopper for me, it's more that it wont returns all the possibilities.

SELECT IdAbonnement,[Courriel],[SiteWeb],[TEL],[CELL],[S.F.],[FAX] FROM (SELECT IdAbonnement,TypeCom.Nom,Valeur FROM Abonnement a LEFT JOIN LnkComClient lnk ON lnk.AbonnementId=a.IdAbonnement INNER JOIN TypeCom ON TypeCom.IdTypeCom=lnk.TypeComId) AS Src PIVOT (
MAX(Valeur)
FOR Nom IN ([Courriel],[SiteWeb],[TEL],[CELL],[S.F.],[FAX])
) pvt
ORDER BY idAbonnement

I also tried that which call a function which return all the emails in one column in a csv style. The performance is really not there when you have 25000 abonnement (subscriber) which has many emails, phone, etc... as they want :

SELECT IdAbonnement,ISNULL(dbo.GetAllEmailForAbonnementId (IdAbonnement),'') AS Email,ISNULL(dbo.GetAllTelForAbonnementId(IdAbonne ment),'') FROM Abonnement

Here is the code for the function GetAllEmailForAbonnementId :

CREATE FUNCTION [dbo].[GetAllEmailForAbonnementId]
(
-- Add the parameters for the function here
@AbonnementId Int
)
RETURNS varchar(2000)
AS
BEGIN
-- Declare the return variable here
DECLARE @LstValeur varchar(2000)

-- Add the T-SQL statements to compute the return value here
SELECT @LstValeur=COALESCE(@LstValeur + ', ', '') + Valeur FROM LnkComClient Lnk WHERE Lnk.AbonnementId=@AbonnementId AND TypeComId=1

-- Return the result of the function
RETURN @LstValeur

END

If you need more information just reply.

Here are my tables if you want to give it a shot.
-- simplified version
CREATE TABLE [dbo].[Abonnement](
[IdAbonnement] [bigint] IDENTITY(1,1) NOT NULL,
[Nom] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS )
GO

CREATE TABLE [dbo].[LnkComClient](
[IdLnkComClient] [bigint] IDENTITY(1,1) NOT NULL,
[AbonnementId] [bigint] NOT NULL,
[TypeComId] [int] NULL,
[Valeur] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_LnkComClient] PRIMARY KEY CLUSTERED
(
[IdLnkComClient] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TypeCom](
[IdTypeCom] [int] IDENTITY(1,1) NOT NULL,
[Nom] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsNumeroTel] [bit] NOT NULL CONSTRAINT [DF_TypeCom_IsNumeroTel] DEFAULT ((0)),
[Weight] [int] NOT NULL CONSTRAINT [DF_TypeCom_Weight] DEFAULT ((0)),
CONSTRAINT [PK_TypeCom] PRIMARY KEY CLUSTERED
(
[IdTypeCom] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

---