SELECT
I.ITEM_CODE AS CODE,
I.ITEM_NAME AS NAME,
CASE
WHEN I.USER_UPDATE_DATETIME != IH.USER_UPDATE_DATETIME THEN 'UPDATED'
WHEN IH.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM ITEM) THEN 'DELETED'
WHEN I.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM ITEM_HISTORY) THEN 'ADDED'
END AS COMP_RSLT
FROM ITEM AS I
INNER JOIN ITEM_HISTORY AS IH
ON I.ITEM_CODE = IH.ITEM_CODE
ORDER BY CODE DESC