Help with SQL Query

• dt293

  Default port

  Points: 1412

  More actions

  May 14, 2008 at 6:18 am

  #380512

  Hi all,

  I need to compare the rows from table1 to table2 and return the name of the column if any differences are found...

  A quick example;

  Table1

  ID......UserName.......DOB.......Variant

  1.......Joe................1/1/08....True

  Table2

  ID......UserName.......DOB.......Variant

  1.......Bloggs............1/1/08....True

  I would like to compare each field and return the name of the column with the difference (rather than the contents of the coulmn), in this example UserName.

  Any help would be much appreciated.

  Thanks

• brgadishwaran

  SSC Journeyman

  Points: 92

  More actions

  May 14, 2008 at 6:36 am

  #815107

  Hi David,

  Could you please re-check your requirement because i think you need to have a primary key.(l will proceed with the assumption that it is username)

  then you can pivot both tables ( i hope you are familiar with the pivot syntax)

  the result will be

  pivot_table1

  column_name ....... value .....primary_key_column

  ID 1 joe

  UserName joe joe

  DOB 1/1/08 joe

  Variant True joe

  and similliarly for table2

  and then we can use this query

  select t1.column_name ,t1.value

  from pivot_table1 T1 inner join pivot_table2 T2

  on t1.primary_key_column=t2.primary_key_column

  and t1.column_name=t2.column_name

  and t1.value <> t2.value

  you will get both the column name as well as the mis match value.

  does this solve your problem?

  Braga

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  May 14, 2008 at 6:44 am

  #815119

  Try this:

  selectCOALESCE (Table1.id, table2.id) as ID

  ,CASEWHEN Table1.id is null then 'Missing Table1 row'

  WHEN Table2.id is null then 'Missing Table2 row'

  ELSE'Row exists in both tables'

  ENDAS TableRowsMatch

  ,'UserName ' +

  CASEWHEN Table1.UserName = Table2.UserName then 'same'

  WHEN Table1.UserName is null and table2.UserName is null then 'same'

  ELSE'different'

  ENDAS UserNameMatch

  FROMTable1

  FULL OUTER JOIN

  Table2

  on Table1.ID = Table2.ID

  SQL = Scarcely Qualifies as a Language

• dt293

  Default port

  Points: 1412

  More actions

  May 14, 2008 at 7:57 am

  #815206

  Thanks guys, I will give both suggestions a try and report back.