MySql join two tables sequentially

Question

Table one

===================
id    name
-------------------
1     m
2     m
3     a
4     u
5     g

Table two

===================
id    name
-------------------
8     m
9     m
10    u
11    a
12    x
15    m

Expected result

===================
1     m     8
2     m     9
3     a     11
4     u     10

I need to find id from table 2 associated with table 1 by name. But ids from table 2 must be different.

If i make join i receive wrong intersections:

select t1.id as i1, t1.name, t2.id as i2 from t1
 join t2 on t1.name = t2.name



 i1  name i2
--------------------
'1','m','8'
'2','m','8'
'1','m','9'
'2','m','9'
'4','u','10'
'3','a','11'
'1','m','15'
'2','m','15'

I need this for tables synchronization from different systems.

Answer 1

You can use the following query:

SELECT t1.id, t1.name, t2.id
FROM (
   SELECT id, name,
          @rn1 := IF(@n = name, @rn1 + 1,
                    IF(@n := name, 1, 1)) AS rn1
   FROM Table1
   CROSS JOIN (SELECT @rn1 := 0, @n := '') AS vars
   ORDER BY name, id) AS t1
INNER JOIN (
   SELECT id, name,
          @rn2 := IF(@n = name, @rn2 + 1,
                    IF(@n := name, 1, 1)) AS rn2
   FROM Table2
   CROSS JOIN (SELECT @rn2 := 0, @n := '') AS vars
   ORDER BY name, id
) AS t2 ON t1.name = t2.name AND t1.rn1 = t2.rn2
ORDER BY t1.id

The query uses variables in order to simulate ROW_NUMBER() window function, currently not available in MySQL. Variables @rn1, @rn2 enumerate records that belong to the same name partition with an order determined by id field.

Demo here