mr_squall mr_squall -4 years ago 62
MySQL Question

MySql join two tables sequentially

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download