select old records with new data

• Jennifer-470472

  Grasshopper

  Points: 23

  More actions

  September 19, 2007 at 5:30 pm

  #181103

  I want to do somewhat the opposite of my previous post:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=400047

  I have two tables, one with old data, one with some new data. I want to select the records whose data has changed in a particular field.

  At first I thought this would achieve the results I wanted:

  SELECT *

  FROM TableWithNewData new, TableWithOldData old

  WHERE new.col1=old.col1 and new.col2<>old.col2

  But when I verify my results, the count I expect to have does not add up. For example, the record count that I get after having run this query is 366015, but the record count for TableWithNewData is only 365275 and the record count from TableWithOldData is 357895. I just don't expect the query results to give me more records than what is in either of my tables. So, where have I gone wrong?

  ---

  TIA,

  Jennifer
  

• Gabriel Andres Brancolini

  SSC Rookie

  Points: 46

  More actions

  September 19, 2007 at 7:19 pm

  #734778

  Hi

  try this

  SELECT

  *

  FROM

  TableWithNewData inner join TableWithOldData old on new.col1=old.col1

  WHERE

  new.col2 not in (SELECT col1 FROM TableWithOldData)

  Have a nice day!!!