Get specific rows only after comparing with previously added rows on different date-CodePudding

There is a table which has multiple fields including a Date column at which the current record gets updated.

Col1	Col2	Col3	Date
A	X1	Y1	Today
B	X2	Y2	Today
C	X3	X3	Today
A	X1	Y1	Yesterday
B	X4	Y4	Yesterday

I am looking for a good SQL SELECT statement to get the updated and new records only for the current date, in other words for the above table the 2nd and 3rd records would be my answer. 3rd row is the newly created row on that day, and 2nd row has changed Col2 & Col3 as compared to yesterday. It should also handle edge cases.

CodePudding user response：

You can try to use NOT EXISTS subquery to find your logic.

SELECT *
FROM T t1
WHERE Date = 'Today'
AND NOT EXISTS (
    SELECT *
    FROM T tt
    WHERE Date = 'Yesterday'
    AND t1.Col1 = tt.Col1
    AND t1.Col2 = tt.Col2
    AND t1.Col3 = tt.Col3
)

sqliffle

CodePudding user response：

use equivalent of full join in mysql and where condition.

SELECT t1.col1,
       t1.col2,
       t1.col3,
       t1.date
FROM   table  t1
       LEFT JOIN (SELECT *
                  FROM   table 
                  WHERE  date = 'Yesterday') t2
              ON t1.col1 = t2.col1
                 AND t1.col2 = t2.col2
                 AND t1.col3 = t2.col3
WHERE  t1.date = 'Today'
       AND t2.col2 IS NULL
UNION ALL
SELECT t1.col1,
       t1.col2,
       t1.col3,
       t1.date
FROM   table  t1
       RIGHT JOIN (SELECT *
                   FROM   table 
                   WHERE  date = 'Yesterday') t2
               ON t1.col1 = t2.col1
                  AND t1.col2 = t2.col2
                  AND t1.col3 = t2.col3
WHERE  t1.date = 'Today'
       AND t2.col2 IS NULL