How to compare two mysql tables?

Question

I am new in mysql.

I have two completely identical mysql tables (they have similar id).

Table_1

+----+------------+------+
| id | group_name | cell |
+----+------------+------+
|  1 | YE-04      | 12   |
|  2 | AG-11      | -200 |
|  3 | VG-17      | 11   |
+----+------------+------+

Table_2

+----+------------+------+
| id | group_name | cell |
+----+------------+------+
|  1 | YE-04      | NULL |
|  2 | AG-10      | 13   |
|  3 | VG-17      | 11   |
+----+------------+------+

And I would like to.

First, compare the columns (parameters) to the fact of their inconsistencies between themselves.

Second, group them by ID.

Third, output to the third (new) table in this form (display only those parameters that do not correspond to each other):

+----+------------+----------------|----------------+
| id | col_name   | table_1_param  |  table_2_param |
+----+------------+----------------+----------------+
|  1 | cell       | 12             | NULL           |
|  2 | group_name | AG-11          | AG-10          |
|  2 | cell       | -200           | 13             |
+----+------------+----------------+----------------+

I tried to group them using the JOIN and UNION commands, but it did not work for me.

Gordon Linoff · Accepted Answer · 2019-05-06T11:43:09.517

1

You can unpivot using union all and then aggregate:

select id, col, max(val1) as val1, max(val2) as val2
from ((select id, 'group_name' as col, group_name as val1, null as val2
       from table1
      ) union all
      (select id, 'cell' as col, cell as val1, null as val2
       from table1
      ) union all
      (select id, 'group_name' as col, null as val1, group_name as val2
       from table2
      ) union all
      (select id, 'cell' as col, null as val1, cell as val2
       from table2
      ) 
     ) t
group by id, col
having not max(val1) <=> max(val2);

edited May 06 '19 at 11:43

answered May 06 '19 at 10:53

Gordon Linoff

1,242,037
58
646
786

you did what I asked for, but exactly the opposite. – David Grigoryev May 06 '19 at 11:16
@DavidGrigoryev . . . LOL. It is the silly syntax that MySQL uses for null-safe comparisons. To me, it looks more like `<>` than `=`. – Gordon Linoff May 06 '19 at 11:43
I'm sorry, maybe I misunderstood you. This is the solution I need - thank you! – David Grigoryev May 06 '19 at 11:57

score 0 · Answer 2 · edited May 06 '19 at 07:44

0

You can try below SQL to get job done:

SELECT t1.id, t1.group_name, t1.cell AS "table_1_param", t2.cell AS "table_2_param"
FROM Table_1 t1, Table_2 t2'
WHERE t1.id = t2.id
AND t1.cell != t2.cell

edited May 06 '19 at 07:44

Geee

2,217
15
30

answered May 06 '19 at 07:36

Emily Wotruba

23
5

How to compare two mysql tables?

2 Answers2