too long

Question

Day1
IP Address  Model Number    MAC Address Host Name   Date    Phone Load Version
10.1.1.14   CP-8945 70CA9B010000    SEP70CA9B01001  2012-06-01 SCCP 9-2-2-0
10.1.1.15   CP-8945 70CA9B010001    SEP70CA9B01002  2012-06-01 SCCP 9-2-2-0
10.1.1.16   CP-8945 70CA9B010002    SEP70CA9B01003  2012-06-01 SCCP 9-2-2-0
10.1.1.17   CP-8945 70CA9B010003    SEP70CA9B01004  2012-06-01 SCCP 9-2-2-0

Day2
IP Address  Model Number    MAC Address Host Name   Date    Phone Load Version
10.1.1.14   CP-8945 70CA9B010000    SEP70CA9B01001  2012-06-02 SCCP 9-2-2-0
10.1.1.15   CP-8945 70CA9B010001    SEP70CA9B01002  2012-06-02 SCCP 9-2-2-0
10.1.1.16   CP-8945 70CA9B010002    SEP70CA9B01003  2012-06-02 SCCP 9-2-2-0
10.1.1.17   CP-8945 70CA9B010003    SEP70CA9B01004  2012-06-02 SCCP 9-2-2-1

Result
10.1.1.17   CP-8945 70CA9B010003    SEP70CA9B01004  2012-06-02 SCCP 9-2-2-1

I am not sure if this is possible. I have a MySQL table with multiple matching rows except date. How would I compare 2 dates and list the differences in any column?

Answer 1

I tried doing same scenario before in SQL Trigger and this is what I've done.

SELECT 
    d2.ip_add,
    d2.mdl_num,
    d2.mac_add,
    d2.host_name,
    d2.date,
    d2.phone,
    d2.load_ver
From 
    day2 d2
LEFT JOIN
    day1 d1
ON
d1.ip_add = d2.ip_add
WHERE
    d2.ip_add <> d1.ip_add
OR 
    d2.mdl_num <> d1.mdl_num
OR
    d2.mac_add <> d1.mac_add
OR
    d2.host_name <> d1.host_name
OR
    d2.phone <> d1.phone
OR
    d2.load_ver <> d1.load_ver; 

This will return the difference between two identical tables.

I edit my query so that it will only look on 1 table. Try this one,

SELECT 
    d2.ip_add,
    d2.mdl_num,
    d2.mac_add,
    d2.host_name,
    d2.date,
    d2.phone,
    d2.load_ver
FROM 
        (SELECT 
            ip_add,
            mdl_num,
            mac_add,
            host_name,
            DATE,
            phone,
            load_ver,
            day
         FROM
            tblRecords
         WHERE
            day = 2
         ) AS d2
LEFT JOIN
        (SELECT 
            ip_add,
            mdl_num,
            mac_add,
            host_name,
            DATE,
            phone,
            load_ver,
            day
         FROM
            tblRecords
         WHERE
             day = 1
        ) AS d1
ON
    d1.ip_add = d2.ip_add
WHERE
    d2.ip_add <> d1.ip_add
OR 
    d2.mdl_num <> d1.mdl_num
OR
    d2.mac_add <> d1.mac_add
OR
    d2.host_name <> d1.host_name
OR
    d2.phone <> d1.phone
OR
    d2.load_ver <> d1.load_ver;