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