Comparing 2 columns in mysql [closed]

im having 2 tables with bellow data (Not match data can be in amount1 or amount2)

date        ID  amount1   amount2
11-2-16     2     2         0
11-2-16     1     5         11
11-2-16     3     0         9
12-2-16     2     4         0
12-2-16     1     4         0

Table 2

Bdate     BID  Bamount1   Bamount2
11-2-16     1     5         11
11-2-16     2     1         0
11-2-16     3     0         6
12-2-16     1     4         0
12-2-16     2     1         0

what i need is to show the data which is not match in both table like below

Date       id     Amoun1   Bamount1        Amount2     Bamount
11-2-16     2        2        1        
11-2-16     3                                  9          6
12-2-16     2        4        1                           

Appreciate your help

Create table/insert data

CREATE TABLE Table1
    (`date` VARCHAR(7), `ID` INT, `amount1` INT, `amount2` INT)
;

INSERT INTO Table1
    (`date`, `ID`, `amount1`, `amount2`)
VALUES
    ('11-2-16', 2, 2, 0),
    ('11-2-16', 1, 5, 11),
    ('11-2-16', 3, 0, 9),
    ('12-2-16', 2, 4, 0),
    ('12-2-16', 1, 4, 0)
;


CREATE TABLE Table2
    (`Bdate` VARCHAR(7), `BID` INT, `Bamount1` INT, `Bamount2` INT)
;

INSERT INTO Table2
    (`Bdate`, `BID`, `Bamount1`, `Bamount2`)
VALUES
    ('11-2-16', 1, 5, 11),
    ('11-2-16', 2, 1, 0),
    ('11-2-16', 3, 0, 6),
    ('12-2-16', 1, 4, 0),
    ('12-2-16', 2, 1, 0)
;

Query

Join on date and id and use where to filter out where the fields don't match.

SELECT 
   Table1.date AS "Date"
 , Table1.id
 , (CASE WHEN Table1.Amount1 > 0 THEN Table1.Amount1 ELSE NULL END) AS "Amount1"
 , (CASE WHEN Table2.Bamount1 > 0 THEN Table2.Bamount1 ELSE NULL END) AS "Bamount1"
 , (CASE WHEN Table1.amount2 > 0 THEN Table1.amount2 ELSE NULL END) AS "amount2"
 , (CASE WHEN Table2.Bamount2 > 0 THEN Table2.Bamount2  ELSE NULL END) AS "Bamount2"  
FROM 
 Table1 
INNER JOIN
 Table2
ON
   Table1.date = Table2.Bdate 
 AND
   Table1.id = Table2.Bid
WHERE
     Table1.amount1 != Table2.Bamount1
   OR
     Table1.amount2 != Table2.Bamount2 

Result

Date         id  Amount1  Bamount1  amount2  Bamount2  
-------  ------  -------  --------  -------  ----------
11-2-16       2        2         1   (NULL)      (NULL)
11-2-16       3   (NULL)    (NULL)        9           6
12-2-16       2        4         1   (NULL)      (NULL)

Do you want rows where both don't match or just where one doesn't. If its the second, use this, if the first, change the or to an AND

select * from table1 as t1 join table2 as t2 on t1.ID = t2.BID where t1.amount1 != t2.Bamount1 or t2.amount2 != t2.Bamount2