Danielle Dara Galvez Danielle Dara Galvez - 2 months ago 8
MySQL Question

Compare two tables and find the unmatch record

I would like to compare two different tables with almost desame column names and output them by marking 1 on columns with same value and 0 with indifferent values given the ID columns are untouched.

Table 1

ID | Value | Color
1 | good | red
2 | bad | blue


Table 2

ID | Value | Color
1 | any | red
2 | bad | blue


Output

ID | Value | Color
1 | 0 | 1
2 | 1 | 1


wherein, the 0 is the value with different column values.

Answer
SELECT table1.id, 
  CASE WHEN table1.value=table2.value THEN 1 ELSE 0 END AS value,
  CASE WHEN table1.color=table2.color THEN 1 ELSE 0 END AS color
FROM table1 JOIN table2 ON table2.id=table1.id
ORDER BY 1;

or shorter approach:

SELECT table1.id, (table1.value=table2.value) AS value, (table1.color=table2.color) AS color
FROM table1 JOIN table2 ON table2.id=table1.id
ORDER BY 1;

Approach in MySQL-syntax:

SELECT table1.id,
  IF(table1.value=table2.value, 1, 0) AS value,
  IF(table1.color=table2.color, 1, 0) AS color
FROM table1 JOIN table2 ON table2.id=table1.id
ORDER BY 1;