SQL server 2008, get a new table by selecting two columns from two different tables that do not share same column information -

this question based on

sql 2008 r2 cte syntax error in select statement

on sql server 2008, need new table selecting 2 columns 2 different tables.

  address (from another_table)    id_num (from cte created me)   city_1                        65    city_1                        36   city_2                        65   city_2                        36   city_3                        65   city_3                        36 

suppose id_num has 65 , 36 2 values. cte has no column of "address". another_table has no column of id_num.

for each address, need associate id_num in cte address

any appreciated.

if isn't join, , want associate id_nums addresses, use:

select distinct address, id_num another_table, cte 

if want id_num associated addresses match criteria add clause:

where cte.field1 = address.field1