map non unique attribute to a unique attribute

Question

I have a mapping table that uses a non unique attribute. I need a way to map that attribute to one of the attributes it is referring too, to get the unique attribute. Here is an example of what i am trying to do:

DECLARE @TABLE1  TABLE (id int, table1_name varchar(max))
DECLARE @TABLE2  TABLE (id int, table2_name varchar(max))
DECLARE @TABLE1_TABLE2 TABLE (table1_name varchar(max), table2_name varchar(max))

insert into @TABLE1 (id, table1_name) values (1, 'table1_name1')
insert into @TABLE1 (id, table1_name) values (2, 'table1_name2')
insert into @TABLE1 (id, table1_name) values (3, 'table1_name3')

insert into @TABLE2 (id, table2_name) values (1, 'table2_name1')
insert into @TABLE2 (id, table2_name) values (2, 'table2_name2')
insert into @TABLE2 (id, table2_name) values (3, 'table2_name2')

insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name1', 
    'table2_name1')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name2', 
    'table2_name2')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name3',     
    'table2_name2')


select t1.id as t1id,min(t2.id) as t2id
    from @TABLE1_TABLE2 tt
    inner join @TABLE1 t1 on tt.table1_name = t1.table1_name
    inner join @TABLE2 t2 on tt.table2_name = t2.table2_name
    GROUP BY t1.id

This returns:

1  1
2  2
3  2

I need it to be either

1  1
2  2
3  3

or

1  1
2  3
3  2

I was thinking my best bet would be to write an aggregate function, but CLR is not an option right now. Rebuilding the relationship is not an option either, as we are stealing it from another database so the pk doesn't map, only the name. The only thing that is working is doing it in a cursor, which unsurprisingly is slow enough that I have to get it set-wise.

EDIT:

If there are multiple entries of TABLE1, then this will breakdown. For instance if we use this for TABLE1_TABLE2 instead (notice that we had to keep the old, no longer valid pk):

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (123451234, 
    'table1_name1','table2_name1')
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (4563456, 
    'table1_name1','table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values 
    (3563456,'table1_name2', 'table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values 
    (4563456,'table1_name3', 'table2_name2')  
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values 
    (123451234,'table1_name3', 'table2_name1')

Then to get the query to work, you have to map the old pk to a new one, like so:

 select * from @TABLE1_TABLE2 ga
    inner join ( select oldid, id as newid from (
    select oldid,max(table2_name) as table2_name, row_number() over(partition by 
        max(table2_name) order by oldid) as rn from @TABLE1_TABLE2
    GROUP BY oldid ) tmp
inner join ( select table2_name,id, row_number() over(partition by table2_name order by 
    id) as rn from @TABLE2) g on tmp.table2_name = g.table2_name and g.rn = tmp.rn ) 
    ids on ga.oldid = ids.oldid
inner join @TABLE2 g on ids.newid = g.id
inner join @TABLE1 a on ga.table1_name = a.table1_name

Thanks for the help! Let me know if the edit is off topic now, wanted to share my discovery in case it saves someone some frustration.

Answer 1

This will work with the sample data you have provided here. Your data could be entered into your tables in a way so that this will not work. If that is the case, please update your question with data that will show the issues you have.

;with TT as
(
  select table1_name,
         table2_name,
         row_number() over(partition by table2_name order by (select 1)) as rn
  from @TABLE1_TABLE2
),
T2 as
(
  select id,
         table2_name,
         row_number() over(partition by table2_name order by id) as rn
  from @TABLE2
)
select T1.id as T1ID,
       T2.id as T2ID
from TT
  inner join @TABLE1 as T1
    on TT.table1_name = T1.table1_name
  inner join T2
    on TT.table2_name = T2.table2_name and
       TT.rn = T2.rn