Match on one field to many

1

The question of the week...
I have 2 tables, I need to join on ID, then compare the email from T1 to the 10 possible emails in T2 and display table ID and what column it matches in table 2.

Here is sample data

Create table #t1
(id int,EmailAddress varchar(50))

insert into #t1
values
(1,'E1@Domain.com'),(2,'E3@Domain.com'),(3,'E5@Domain.com'),(4,'E7@Domain.com'),(5,'E9@Domain.com'),(6,'E2@Domain.com'),(7,'E4@Domain.com'),(8,'E6@Domain.com'),(9,'E8@Domain.com'),(10,'E10@Domain.com')

Create table t2
(id int,
EmailAddress1 varchar(50), EmailAddress2 varchar(50),
EmailAddress3 varchar(50), EmailAddress4 varchar(50),
EmailAddress5 varchar(50), EmailAddress6 varchar(50),
EmailAddress7 varchar(50), EmailAddress8 varchar(50),
EmailAddress9 varchar(50), EmailAddress10 varchar(50))

insert into t2
values
(1,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(2,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(3,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(4,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(5,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(6,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(7,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(8,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(9,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com'),
(10,'E1@Domain.com','E2@Domain.com','E3@Domain.com','E4@Domain.com','E5@Domain.com','E6@Domain.com','E7@Domain.com','E8@Domain.com','E9@Domain.com','E10@Domain.com')

2

Something like this?

Declare @t1 Table (
        id int
      , EmailAddress varchar(50)
        );

 Insert Into @t1 (id, EmailAddress)
 Values (1, 'E1@Domain.com')
      , (2, 'E3@Domain.com')
      , (3, 'E5@Domain.com')
      , (4, 'E7@Domain.com')
      , (5, 'E9@Domain.com')
      , (6, 'E2@Domain.com')
      , (7, 'E4@Domain.com')
      , (8, 'E6@Domain.com')
      , (9, 'E8@Domain.com')
      , (10, 'E10@Domain.com');

Declare @t2 Table (
        id int
      , EmailAddress1 varchar(50)
      , EmailAddress2 varchar(50)
      , EmailAddress3 varchar(50)
      , EmailAddress4 varchar(50)
      , EmailAddress5 varchar(50)
      , EmailAddress6 varchar(50)
      , EmailAddress7 varchar(50)
      , EmailAddress8 varchar(50)
      , EmailAddress9 varchar(50)
      , EmailAddress10 varchar(50)
        );

 Insert Into @t2
 Values (1, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (2, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (3, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (4, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (5, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (6, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (7, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (8, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (9, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com')
      , (10, 'E1@Domain.com', 'E2@Domain.com', 'E3@Domain.com', 'E4@Domain.com', 'E5@Domain.com', 'E6@Domain.com', 'E7@Domain.com', 'E8@Domain.com', 'E9@Domain.com', 'E10@Domain.com');

 Select * From @t1 t;
 Select * From @t2 t;

 Select t.id
      , t.EmailAddress
      , e.EmailID
      , e.EmailAddress
   From @t1 t
  Inner Join @t2 t2 On t2.id = t.id
  Cross Apply (
 Values (1, t2.EmailAddress1)
      , (2, t2.EmailAddress2)
      , (3, t2.EmailAddress3)
      , (4, t2.EmailAddress4)
      , (5, t2.EmailAddress5)
      , (6, t2.EmailAddress6)
      , (7, t2.EmailAddress7)
      , (8, t2.EmailAddress8)
      , (9, t2.EmailAddress9)
      , (10, t2.EmailAddress10)
              ) As e(EmailID, EmailAddress)
  Where e.EmailID = t.id
3

The results they are looking for is

id Column
1 EmailAddress1
2 EmailAddress3
3 EmailAddress5
4 EmailAddress7
5 EmailAddress9
6 EmailAddress2
7 EmailAddress4
8 EmailAddress6
9 EmailAddress8
10 EmailAddress10

They only want the ID and what field the email match is.

4

Okay - I thought you were matching by position, but you are matching by address. So - change the where clause to match on address:

Where e.EmailAddress = t.EmailAddress

5

Is there a way to put the column from T2 in the results with the ID for T1?
I was able to do the join, just not get the column name from T1

Thanks for all you input. I am stumped

6

I don't follow - what value are you trying to get?

Something like this?

 Select t.id
      , t.EmailAddress
      , e.EmailID
      , AddressColumn = concat('EmailAddress', e.EmailID)
      , e.EmailAddress
   From @t1 t
  Inner Join @t2 t2 On t2.id = t.id
  Cross Apply (
 Values (1, t2.EmailAddress1)
      , (2, t2.EmailAddress2)
      , (3, t2.EmailAddress3)
      , (4, t2.EmailAddress4)
      , (5, t2.EmailAddress5)
      , (6, t2.EmailAddress6)
      , (7, t2.EmailAddress7)
      , (8, t2.EmailAddress8)
      , (9, t2.EmailAddress9)
      , (10, t2.EmailAddress10)
              ) As e(EmailID, EmailAddress)
  Where e.EmailAddress = t.EmailAddress
7 
  select 
	T1.id,
	UPVT.ColumnName
  from @t2 T2
  Unpivot
  (
	EmailAddress For ColumnName In ([EmailAddress1],[EmailAddress2],[EmailAddress3],[EmailAddress4],[EmailAddress5],
	[EmailAddress6],[EmailAddress7],[EmailAddress8],[EmailAddress9],[EmailAddress10])
  ) UPVT
  Join @t1 T1 On UPVT.id = T1.id
	And UPVT.EmailAddress = T1.EmailAddress
8

Andy,
Thanks that worked.