CODEWITHSUNDEEP
mysqlleft-joinrelevance
Epsiloncool
Epsiloncool

Reputation: 1473

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

Upvotes: 0

Views: 266

Answers (4)

LukStorms
LukStorms

Reputation: 29677

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.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT id
     , SUM(weight) * CASE WHEN COUNT(*)=1 THEN 1 ELSE 10 END relevance 
  FROM
     ( SELECT id
            , weight 
         FROM table1 
        UNION 
          ALL 
       SELECT id
            , weight 
         FROM table2
     ) x
 GROUP 
    BY id;
+----+-----------+
| id | relevance |
+----+-----------+
|  1 |      0.10 |
|  2 |      0.15 |
|  3 |      2.10 |
|  6 |      4.10 |
|  8 |      0.10 |
| 11 |      0.13 |
+----+-----------+

Upvotes: 1

balakrishnan
balakrishnan

Reputation: 383

We can use stored procedures and temporary tables to get solution

CREATE PROCEDURE GetReleavance()
BEGIN
Create TEMPORARY TABLE tmpList ENGINE=MEMORY
SELECT id, weight from t1
union all 
SELECT id, weight from t2
union all 
SELECT id, weight from t3;

select id, sum(weight)* POW(10,COUNT(1)-1) as relevance 
from tmpList 
group by id;

DROP TEMPORARY TABLE IF EXISTS tmpList;

END

In the procedure creating a temporary table with all id's and weight from different table and get sum(weight) based on id.

Call the stored procedure using

CALL GetReleavance()

You can make Union all for number of table you want also it will not make major impact on performance.

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30849

You can use FULL OUTER JOIN for this, e.g.:

SELECT t1.id AS id1, t1.weight AS weight1, t2.id AS id2, t2.weight AS weight2
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT t1.id AS id1, t1.weight AS weight1, t2.id AS id2, t2.weight AS weight2
FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;

Upvotes: 2

Related Questions