Select SQL Inner Join and Omit Certain Record <p>I have two tables with following data:</p> <p>Table A</p> <pre><code>ID DESC 1 One 2 Two 3 Three </code></pre> <p>ID is primary key</p> <p>Table B shows the action I did to ID in table A</p> <pre><code>NO ACTION ID DATETIME 1 ADD 1 2012-01-01 00:00:00 2 ADD 2 2012-01-01 00:00:00 3 ADD 3 2012-01-01 00:00:00 4 DELETE 2 2012-01-01 01:00:00 5 EDIT 1 2012-01-01 02:00:00 6 EDIT 3 2012-01-01 03:00:00 7 DELETE 1 2012-01-01 03:00:00 8 REVIVE 2 2012-01-01 04:00:00 9 EDIT 2 2012-01-01 05:00:00 </code></pre> <p>NO is primary key</p> <p>Here's the summary:</p> <p>ID 1: ADD on 2012-01-01 00:00:00, EDIT on 2012-01-01 02:00:00, and DELETE on 2012-01-01 03:00:00</p> <p>ID 2: ADD on 2012-01-01 00:00:00, DELETE on 2012-01-01 01:00:00, REVIVE on 2012-01-01 04:00:00, EDIT on 2012-01-01 05:00:00</p> <p>ID 3: ADD on 2012-01-01 00:00:00, EDIT on 2012-01-01 03:00:00</p> <p>How do I query the table to get following result:</p> <p>Table C</p> <pre><code>NO DESC 2 Two 3 Three </code></pre> <p>What I want to do is: to query the Table A that not having DELETE action at the last transaction time so I get only the active ID</p> <p>I tried to do inner join but got stuck on how to omit ID that having delete action at the last transaction time. Any suggestion/comment would be appreciated.</p>