SQLTeam.com | Weblogs | Forums

Many to one join

sql2008r2

#1

Hello, I have two tables:

[code]CREATE TABLE DB1.dbo.I3_Table1_CH0(
i3_identity BIGINT,
reason VARCHAR(80) NOT NULL,
finishcode VARCHAR(255) NOT NULL,
callidkey CHAR(18),
phonenumber VARCHAR(50),
callplacedtime DATETIME,
cadetail VARCHAR(255)
);

CREATE TABLE DB1.dbo.Table1_Attempts(
[I3_IDENTITY] [bigint] NOT NULL PRIMARY KEY CLUSTERED,
[RefNum] varchar NULL,
[Debtor] varchar NULL,
[Phone1] varchar NULL,
[Phone2] varchar NULL,
[Phone3] varchar NULL,
[Phone4] varchar NULL,
[Phone5] varchar NULL,
[Phone6] varchar NULL,
[Phone7] varchar NULL,
[Phone8] varchar NULL,
[Phone9] varchar NULL,
[Phone10] varchar NULL
);[/code]

They are related on I3_Identity and phone number. My problem is the second table has up to ten phone numbers so when I combine the two I end up with:

SELECT DISTINCT C1.i3_identity ,C1.reason ,C1.finishcode ,C1.callidkey ,C1.phonenumber ,C1.callplacedtime ,C1.cadetail ,A1.RefNum ,A1.Debtor FROM DB1.dbo.I3_Table1_CH0 C1 INNER JOIN DB1.dbo.Table1_Attempts A1 ON C1.i3_identity = A1.I3_IDENTITY AND (C1.phonenumber = A1.Phone1 OR C1.phonenumber = A1.Phone2 OR C1.phonenumber = A1.Phone3 OR C1.phonenumber = A1.Phone4 OR C1.phonenumber = A1.Phone5 OR C1.phonenumber = A1.Phone6 OR C1.phonenumber = A1.Phone7 OR C1.phonenumber = A1.Phone8 OR C1.phonenumber = A1.Phone9 OR C1.phonenumber = A1.Phone10 );
Is this a good way to combine them?
Thank you
DJJ


#2

Have a Child Table of [Table1_Attempts] for the Phone numbers (instead of 10 x columns) - then you can have unlimited phone numbers [if you want].

FROM DB1.dbo.I3_Table1_CH0    C1
INNER JOIN DB1.dbo.Table1_Attempts A1 ON C1.i3_identity = A1.I3_IDENTITY 
WHERE EXISTS (
    SELECT *
    FROM DB1.dbo.PhoneNumbers AS PN 
    WHERE PN.i3_identity = C1.I3_IDENTITY 
        AND PN.phonenumber = C1.phonenumber 
)

If you have to have the 10 x Phone1 ... Phone10 columns then you may find that a UNION ALL is faster than the 10 x OR tests in your Join


#3

Thank you, I forgot to say the two tables are not of my making, but a third party's that I am getting data from.

I will look into your suggestions of creating a table of phone numbers or possible using UNION ALL.

Thank you again.


#4

I guessed that was probably the case :slight_smile:

Perhaps you could duplicate the Column-based data into a row-based table (of your making :slight_smile:) using a trigger, such that a) it was in Sync and b) it was efficient to search?