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.