Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to join and one only match one by SQL

I have two tables as below.
I want to update table_a.fid to table_b.id when table_a.name=table_b.name and table_a.value=table_b.value and table_b.id is not set to other table_a.fid

table_a (a):

id(int) name(varchar) value(varchar) fid(int)
1       name_a        value_1        0
2       name_a        value_1        0
3       name_a        value_1        0
4       name_b        value_2        0
5       name_c        value_3        0

table_b (b):

id(int) name(varchar) value(varchar)
1       name_a        value_1
2       name_a        value_1
3       name_b        value_2
4       name_b        value_2

table_a expected results:

id name    value   fid
1  name_a  value_1  1 // b.id(1,2) matched, so set the first b.id(1) to a.fid(1)
2  name_a  value_1  2 // b.id(1,2) matched and b.id(1) already set to a.fid(1), so set the second b.id(2) to a.fid(2)
3  name_a  value_1  0 // b.id(1,2) matched and b.id(1,2) already set to a.fid(1,2), so is 0
4  name_b  value_2  3 // b.id(3,4) matched. so set the first b.id(3) to a.fid
5  name_c  value_3  0 // b not matched
Tags:

2

Answers


  1. 0

    If you are using mysql 8.0 or higher then you can use window function ROW_NUMBER() to assign an order number to rows within a related record set, and then use this unique identifier for joining as well :

    with cte_a as (
  select *, row_number() over (partition by name, val order by id) as rn
  from table_a
),
cte_b as (
  select *, row_number() over (partition by name, val order by id) as rn
  from table_b
)
select cte_a.id, cte_a.name, cte_a.val, coalesce(cte_b.id, 0) as fid
from cte_a
left join cte_b using(name, val, rn);

    Results :

    id  name    val      fid
1   name_a  value_1  1
2   name_a  value_1  2
3   name_a  value_1  0
4   name_b  value_2  3
5   name_c  value_3  0

    Demo here

    Login or Signup to reply.
  2. 0

    For MySQL 5.7,try the query below. Note: the user variable @row_number defines row number for a row of the current name, and the @row_number value is reset to 1 when @current_name meets a different name in the row.

    select ta.id,ta.name,ta.value , coalesce(tb.id,0) as fid
from (select table_a.*,
     @row_number_a:=case when @current_name_a=name then @row_number_a+1 else 1 end as row_num_a,
     @current_name_a:=case when @current_name_a=name then @current_name_a else name end as current_nm_a
     from table_a,(select @row_number_a:=0,@current_name_a:='') t ) ta
left join
     (select table_b.*,
     @row_number_b:=case when @current_name_b=name then @row_number_b+1 else 1 end as row_num_b,
     @current_name_b:=case when @current_name_b=name then @current_name_b else name end as current_nm_b
     from table_b,(select @row_number_b:=0,@current_name_b:='') t ) tb
on ta.name=tb.name and ta.row_num_a=tb.row_num_b
;

-- result set
+------+--------+---------+-----+
| id   | name   | value   | fid |
+------+--------+---------+-----+
|    1 | name_a | value_1 |   1 |
|    2 | name_a | value_1 |   2 |
|    3 | name_a | value_1 |   0 |
|    4 | name_b | value_2 |   3 |
|    5 | name_c | value_3 |   0 |
+------+--------+---------+-----+

    Note, the above query is written supposing rows with the same NAME are in consecutive order. If that’s not case, use a derived table with ORDER BY clause instead of the base table.Take the case below for example.

    create table table_a (id int, name varchar(20), value varchar(10), fid int);
insert table_a values
(1       ,'name_a',        'value_1',        0),
(4       ,'name_b',        'value_2',        0),
(3       ,'name_a',        'value_1',        0),
(5       ,'name_c',        'value_3',        0),
(2       ,'name_a',        'value_1',        0);

create table table_b (id int, name varchar(20), value varchar(10));
insert table_b values (1       ,'name_a',        'value_1'),
(2       ,'name_a',        'value_1'),
(3       ,'name_b',        'value_2'),
(4       ,'name_b',        'value_2'); 

select * from table_a;
-- now we have a result set in which the same names are not consecutively arranged.
+------+--------+---------+------+
| id   | name   | value   | fid  |
+------+--------+---------+------+
|    1 | name_a | value_1 |    0 |
|    4 | name_b | value_2 |    0 |
|    3 | name_a | value_1 |    0 |
|    5 | name_c | value_3 |    0 |
|    2 | name_a | value_1 |    0 |
+------+--------+---------+------+

    Let’s try the original method:

    select ta.id,ta.name,ta.value , coalesce(tb.id,0) as fid
from (select table_a.*,
     @row_number_a:=case when @current_name_a=name then @row_number_a+1 else 1 end as row_num_a,
     @current_name_a:=case when @current_name_a=name then @current_name_a else name end as current_nm_a
     from table_a,(select @row_number_a:=0,@current_name_a:='') t ) ta
left join
     (select table_b.*,
     @row_number_b:=case when @current_name_b=name then @row_number_b+1 else 1 end as row_num_b,
     @current_name_b:=case when @current_name_b=name then @current_name_b else name end as current_nm_b
     from table_b,(select @row_number_b:=0,@current_name_b:='') t ) tb
on ta.name=tb.name and ta.row_num_a=tb.row_num_b
;

-- result
+------+--------+---------+-----+
| id   | name   | value   | fid |
+------+--------+---------+-----+
|    1 | name_a | value_1 |   1 |
|    4 | name_b | value_2 |   3 |
|    3 | name_a | value_1 |   1 |
|    5 | name_c | value_3 |   0 |
|    2 | name_a | value_1 |   1 |
+------+--------+---------+-----+

    As denoted above, the result we get is not right. Let’s query a derived table with an ORDER BY clause (i.e. select * from table_x order by name,id) instead of the base table:

    select ta.id,ta.name,ta.value , coalesce(tb.id,0) as fid
from (select tba.*,
     @row_number_a:=case when @current_name_a=name then @row_number_a+1 else 1 end as row_num_a,
     @current_name_a:=case when @current_name_a=name then @current_name_a else name end as current_nm_a
     from (select * from table_a order by name,id) tba, (select @row_number_a:=0,@current_name_a:='') t ) ta
left join
     (select tbb.*,
     @row_number_b:=case when @current_name_b=name then @row_number_b+1 else 1 end as row_num_b,
     @current_name_b:=case when @current_name_b=name then @current_name_b else name end as current_nm_b
     from (select * from table_b order by name,id) tbb, (select @row_number_b:=0,@current_name_b:='') t ) tb
on ta.name=tb.name and ta.row_num_a=tb.row_num_b
;

-- result is correct now: 
+------+--------+---------+-----+
| id   | name   | value   | fid |
+------+--------+---------+-----+
|    1 | name_a | value_1 |   1 |
|    2 | name_a | value_1 |   2 |
|    3 | name_a | value_1 |   0 |
|    4 | name_b | value_2 |   3 |
|    5 | name_c | value_3 |   0 |
+------+--------+---------+-----+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search