Compare values of two database tables -> MySQL

问题: I looked through various topics here on StackOverflow but I was unable to find the right solution for my problem. Here is what I´ve got: I have two database tables. One t...

问题:

I looked through various topics here on StackOverflow but I was unable to find the right solution for my problem. Here is what I´ve got:

I have two database tables. One table should be some kind of a reference model and looks similar to this example:

+----+----------------+----------------+----------------+-----------------+
| ID |     FIELD1     |     FIELD2     |     FIELD3     |     FIELD 4     |
+----+----------------+----------------+----------------+-----------------+
|  1 | Value1_Field_1 | Value1_Field_2 | Value1_Field_3 | Value1_Field_4  |
|  2 | Value2_Field_1 | Value2_Field_2 | Value2_Field_3 | Value2_Field_4  |
|  3 | Value3_Field_1 | Value3_Field_2 | Value3_Field_3 | Value3_Field_4  |
|  4 | Value4_Field_1 | Value4_Field_2 | Value4_Field_3 | Value4_Field_4  |
|  5 | Value5_Field_1 | Value5_Field_2 | Value5_Field_3 | Value5_Field_4  |
+----+----------------+----------------+----------------+-----------------+

Now I enter new data to a second table. Those data can have the same values and also the same amount of rows. But it can also happen, that some data have more rows or the values inside the rows are different. Here is another table example where I have one row more and two values are different:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  1 | Value1_Field_1 | Value1_Field_2  | Value1_Field_3  | Value1_Field_4 |
|  2 | Value2_Field_1 | Value2_Field_2  | Value2_Field_3  | Value2_Field_4 |
|  3 | Value3_Field_1 | Value3_Field_2  | Value3_NEWVALUE | Value3_Field_4 |
|  4 | Value4_Field_1 | Value4_Field_2  | Value4_Field_3  | Value4_Field_4 |
|  5 | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3  | Value5_Field_4 |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

I am looking for a SQL-Statement which compares those two tables and list all different records. In my example above the SQL-Statement should retourn those informations:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  3 |                |                 | Value3_NEWVALUE |                |
|  5 |                | Value5_NEWVALUE |                 |                |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

Here is what I´ve tried so far:

SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4 from table_references
union
SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4  from table_new_data

That statement returns all rows but only once. This is not what I am looking for. I also tried it with this code:

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM (
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_references
UNION ALL
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_new_data
) tbl
GROUP BY FIELD1
HAVING count(*) = 1
ORDER BY FIELD1

That code only returns row 6 but does not show me the new values inside ID 3 & 5.

Any help would be really appreciated. Thanks in advance!

EDIT: According to @Madhur Bhaiya solution I made a mistake. The output should look like this:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  3 | Value3_Field1  | Value3_Field2   | Value3_NEWVALUE | Value3_Field4  |
|  5 | Value5_Field1  | Value5_NEWVALUE | Value5_Field3   | Value5_Field4  |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

So I also need all values from the affected row where a value is different.


回答1:

We can use LEFT JOIN to compare the two tables and then use conditional functions like If() to get values accordingly:

Query

SELECT tnew.*
FROM table_new_data AS tnew
LEFT JOIN table_references AS told
  ON told.ID = tnew.ID
WHERE (told.ID IS NOT NULL AND 
       (tnew.FIELD1 <> told.FIELD1 OR 
        tnew.FIELD2 <> told.FIELD2 OR 
        tnew.FIELD3 <> told.FIELD3 OR 
        tnew.FIELD4 <> told.FIELD4)
      ) OR 
      told.ID IS NULL;

Result

| ID  | FIELD1         | FIELD2          | FIELD3          | FIELD4         |
| --- | -------------- | --------------- | --------------- | -------------- |
| 3   | Value3_Field_1 | Value3_Field_2  | Value3_NEWVALUE | Value3_Field_4 |
| 5   | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3  | Value5_Field_4 |
| 6   | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |

View on DB Fiddle


回答2:

I believe instead of a Union Clause, you are looking for for the Intersect Clause. Union will report everything in the two tables, once(per the distict modifier). Intersect will only report the commonalities.

  • 发表于 2018-12-25 15:38
  • 阅读 ( 202 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除