JOIN table to itself SQL

Question

Lets say for example I have the table:

-----------------------------------------------
| id  | name | age | hair_color | delete_flag |
-----------------------------------------------
| 1   | Jhon | 20  | black      | 0           |
-----------------------------------------------

Now when a property is changed, lets say Jhon is now 21 we insert a new record form Jhon and we set the delete_flag on the previous to 1. It is an odd workflow but I am trying to keep it simple for the example.

My goal is to find out which property of Jhon is changed. In more complicated version of the question is when we have a Person table, Property_type table and Properties table where in the last table looks like :

Properties

-----------------------------------------------------------
| id | person_id | property_type_id | value | delete_flag |
-----------------------------------------------------------
| ....                                                    |
-----------------------------------------------------------

Here all props of Jhon are different rows and when Jhon for example changes his hair color all previous props (all rows with person_id = jhons_id) are set to deleted (delete_flag = 1) and all props are added again with only difference: hair color prop is different (only 1 row difference).

Now I want to find out which prop has been changed ? Did he changed his hair color or did he passed 21 years. I tried to do this with a somekind of JOIN between Properties and Properties but did no manage to get the result.

PS: I can't add new columns like 'changed' (0/1).

Answer 1

I am guessing that you assign a new ID to John in the process, since your goal seems to have 2 John records in the user table at a certain point (one of them having the delete_flag to true), then you should have another way to spot the John's and I assume it's the name column.

I've got 2 approaches to propose :

You can use MINUS on your property table to find which properties have changed between old john and new john:

SELECT prop_id
  FROM property
 WHERE person_id = oldJOHNid
MINUS
SELECT prop_id
  FROM property
 WHERE person_id = newJOHNid

In the user table, you can compare all properties columns to find the differences for all new/old records, or a specific user

SELECT  TOLD.name, TOLD.id AS old_id, TNEW.id AS new_id,
       (CASE WHEN TOLD.age = TNEW.age THEN 'Age identical' ELSE 'Age Changed' END) age_status,
       ...
       (CASE WHEN TOLD.hair = TNEW.hair THEN 'Hair identical' ELSE 'Hair changed' END) hair_status
  FROM usertable  TOLD,
       usertable TNEW
 WHERE TOLD.name = TNEW.name
     AND TOLD.name='John' -- optional

Answer 2

Try with windows functions lead/lag

Table

        ID NAME        AGE HAIR_                             DELETE_FLAG
---------- ---- ---------- ----- ---------------------------------------
         1 John         20 black                                       1
         2 John         21 black                                       0

select id, name, age, hair_color,
       case 
         when age <> prev_age then 'Age Changed'
         when hair_color <> prev_hair_color then 'Hair Changed'
         else 'Nothing Changes just Rearranges' end as changes,
         prev_age, prev_hair_color
  from (
  select id, name, age, hair_color, 
         lead(age) over (partition by name order by id desc) prev_age,
         lead(hair_color) over (partition by name order by id desc) prev_hair_color,
         delete_flag
    from t
  ) t1
  where delete_flag = 0;

OUPUT

ID         NAME       AGE        HAIR_COLOR CHANGES     PREV_AGE   PREV_HAIR_COLOR             
2          John       21         black      Age Changed 20         black