mysqlleft-joinrelevance

Left join will null in MySQL


I am looking for a way to create a relative relevance function when looking for values in two (or more) different tables. So I have tables like this

table1:

id weight
1  0.1
2  0.15
3  0.12
6  0.21

table2:

id weight
3  0.09
6  0.2
8  0.1
11 0.13

I need to get a relevance function from these two tables by merging them. Same row ids will get 10x relevance and rows with ids from only one table will get "weight" relevance.

Here is an intermediate table which I need to get (and my question is HOW to make such a table):

id1  weight1  id2  weight2
1    0.1      null null
2    0.15     null null
3    0.12     3    0.09
6    0.21     6    0.2
null null     8    0.1
null null     11   0.13

Using this table I can calculate the relevance whatever I need, but the problem is to create such table from these two. Could you help me?

I tried with LEFT JOIN, STRAIGHT_JOIN, LEFT OUTER JOIN, but they make very different results.

Edit: If it matters, I presently envisage the final table to look something like this:

id relevance
 1 0.1
 2 0.15
 3 2.1
 6 4.1
 8 0.1
11 0.13

Solution

  • Here's a few examples :

    create table Table1 (
     id int primary key not null,
     weight decimal(10,2) not null default 0
    );
    
    create table Table2 (
     id int primary key not null,
     weight decimal(10,2) not null default 0
    );
    
    insert into Table1 (id, weight) values
     (1, 0.10)
    ,(2, 0.15)
    ,(3, 0.12)
    ,(6, 0.21)
    ;
    
    insert into Table2 (id, weight) values
     (3,  0.09)
    ,(6,  0.20)
    ,(8,  0.10)
    ,(11, 0.13)
    ;
    
    select 
    id12.id as id,
    t1.id as id1,
    t1.weight as weight1,
    t2.id as id2,
    t2.weight as weight2
    from (select id from Table1 union select id from Table2) id12
    left join Table1 t1 on t1.id = id12.id
    left join Table2 t2 on t2.id = id12.id
    ;
    
    id |  id1 | weight1 |  id2 | weight2
    -: | ---: | ------: | ---: | ------:
     1 |    1 |    0.10 | null |    null
     2 |    2 |    0.15 | null |    null
     3 |    3 |    0.12 |    3 |    0.09
     6 |    6 |    0.21 |    6 |    0.20
     8 | null |    null |    8 |    0.10
    11 | null |    null |   11 |    0.13
    
    select 
    id12.id as id,
    coalesce(t1.weight,0) + coalesce(t2.weight,0) as relevance
    from (select id from Table1 union select id from Table2) id12
    left join Table1 t1 on t1.id = id12.id
    left join Table2 t2 on t2.id = id12.id
    order by id12.id;
    
    id | relevance
    -: | --------:
     1 |      0.10
     2 |      0.15
     3 |      0.21
     6 |      0.41
     8 |      0.10
    11 |      0.13
    
    select id, sum(weight) as relevance
    from
    (
     select id, weight from Table1
     union all 
     select id, weight from Table2
    ) q
    group by id
    order by id;
    
    id | relevance
    -: | --------:
     1 |      0.10
     2 |      0.15
     3 |      0.21
     6 |      0.41
     8 |      0.10
    11 |      0.13
    

    db<>fiddle here

    The 2nd & 3th queries return the same result.
    Which is better?
    That would depend on how many extra fields and/or extra calculations are required.