outer join on same table sql
I am creating a time machine with c#. A time machine is a way of creating a backup of my files in the way where I can access a specific file like it was at a specific time. Anyways the way I am doing so is by looking for all the files inside a directory and I store those files information in a table named table1. So if the first time I scan my computer lets assume I only have 3 files therefore my table will look something like:

    ID   FullName   DateModified   DateInsertedToDatabase
     1     C:\A       456588731             0
     2     C:\B       955588762             0
     3     C:\C       854587783             0

lets say that next time I perform a back up I have the same 3 files but I have created a new file and modified file C. As a result my table should now look like:

        ID   FullName   DateModified   DateInsertedToDatabase
         1     C:\A       456588731             0
         2     C:\B       955588762             0
         3     C:\C       854587783             0
         4     C:\A       456588731             1
         5     C:\B       955588762             1
         6     C:\C       111122212             1
         7     C:\X       123212321             1

now I will like to copy file C and File X because those are the files that have been changed or created. How could I build a query where I could obtain file X and file C ? In other words I want to get all the files that have a DateInsertedToDatabase = 1 and that don't match files where DateInsertedToDatabase is less than 1. 


if I am not being clear here is the continuation of my example:
lets say that I continue with my example and I delete files: B and C, I modify file X, I create a new file Z.  My table should look like:

        ID   FullName   DateModified   DateInsertedToDatabase
         1     C:\A       456588731             0
         2     C:\B       955588762             0
         3     C:\C       854587783             0
         4     C:\A       456588731             1
         5     C:\B       955588762             1
         6     C:\C       111122212             1
         7     C:\X       123212321             1
         8     C:\A       456588731             2
         9     C:\X       898989898             2
         10    C:\Z       789564545             2

here I will like to get files X and Z because file X was modified and File Z was created. I will not want to get file A because that file already exist with the same DateModified. How could I build that query?


outer join on same table sql
===

I am creating a time machine with c#. A time machine is a way of creating a backup of my files in the way where I can access a specific file like it was at a specific time. Anyways the way I am doing so is by looking for all the files inside a directory and I store those files information in a table named table1. So if the first time I scan my computer lets assume I only have 3 files therefore my table will look something like:

    ID   FullName   DateModified   DateInsertedToDatabase
     1     C:\A       456588731             0
     2     C:\B       955588762             0
     3     C:\C       854587783             0

lets say that next time I perform a back up I have the same 3 files but I have created a new file and modified file C. As a result my table should now look like:

        ID   FullName   DateModified   DateInsertedToDatabase
         1     C:\A       456588731             0
         2     C:\B       955588762             0
         3     C:\C       854587783             0
         4     C:\A       456588731             1
         5     C:\B       955588762             1
         6     C:\C       111122212             1
         7     C:\X       123212321             1

now I will like to copy file C and File X because those are the files that have been changed or created. How could I build a query where I could obtain file X and file C ? In other words I want to get all the files that have a DateInsertedToDatabase = 1 and that don't match files where DateInsertedToDatabase is less than 1. 


if I am not being clear here is the continuation of my example:
lets say that I continue with my example and I delete files: B and C, I modify file X, I create a new file Z.  My table should look like:

        ID   FullName   DateModified   DateInsertedToDatabase
         1     C:\A       456588731             0
         2     C:\B       955588762             0
         3     C:\C       854587783             0
         4     C:\A       456588731             1
         5     C:\B       955588762             1
         6     C:\C       111122212             1
         7     C:\X       123212321             1
         8     C:\A       456588731             2
         9     C:\X       898989898             2
         10    C:\Z       789564545             2

here I will like to get files X and Z because file X was modified and File Z was created. I will not want to get file A because that file already exist with the same DateModified. How could I build that query?

