problem in merging 2 sql queries into a single query to distinguish old and new.plz help!!

• suvo.kundu

  SSC Eights!

  Points: 953

  More actions

  January 12, 2013 at 6:32 am

  #99797

  in my table i have the records like

  Id Record1 Record2 Level gid

  1testRec1 testRec2 0 1

  2testRec11 testRec22 0 2

  3testRec111 testRec222 0 3

  4testRec111 testRec2222 0 4

  5newtestRec newtestRec2 1 1

  I want to show records like

  Id OldRecord1 NewRecord1 OldRecord2 NewRecord2

  1 testRec1 newtestRec1 testRec2 newtestRec2

  2 testRec11 NoNewRecord testRec22 NoNewRecord

  3 testRec22 NoNewRecord testRec33 NoNewRecord

  4 testRec33 NoNewRecord testRec33 NoNewRecord

  actually i have 2 querys

  this is for old record

  select * from tablename where level=0

  this is for New record

  select * from tablename where level=1

  I want to merge this into a single record set as old and new..plz help

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  January 12, 2013 at 8:17 am

  #1576576

  suvo.kundu (1/12/2013)

  ---

  in my table i have the records like

  Id Record1 Record2 Level gid

  1testRec1 testRec2 0 1

  2testRec11 testRec22 0 2

  3testRec111 testRec222 0 3

  4testRec111 testRec2222 0 4

  5newtestRec newtestRec2 1 1

  I want to show records like

  Id OldRecord1 NewRecord1 OldRecord2 NewRecord2

  1 testRec1 newtestRec1 testRec2 newtestRec2

  2 testRec11 NoNewRecord testRec22 NoNewRecord

  3 testRec22 NoNewRecord testRec33 NoNewRecord

  4 testRec33 NoNewRecord testRec33 NoNewRecord

  actually i have 2 querys

  this is for old record

  select * from tablename where level=0

  this is for New record

  select * from tablename where level=1

  I want to merge this into a single record set as old and new..plz help

  SELECT *

  FROM tablename old

  INNER JOIN tablename new

  ON old.id = new.id

  WHERE old.LEVEL = 0

  AND new.LEVEL = 1;

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• suvo.kundu

  SSC Eights!

  Points: 953

  More actions

  January 12, 2013 at 8:23 am

  #1576579

  ya i could get my solution as

  select

  ZZ.gid,

  ZZ.Record1 as 'OldRecord1',

  CASE WHEN XX.Record1 IS NULL THEN 'No New Record' ELSE XX.Record1 END as 'NewRecord1' ,

  ZZ.Record2 as 'OldRecord2',

  CASE WHEN XX.Record2 IS NULL THEN 'No New Record' ELSE XX.Record2 END as 'NewRecord2'

  --XX.Record1 as 'NewRecord1',

  --XX.Record2 as 'NewRecord2'

  from

  (

  SELECT

  ROW_NUMBER ( ) OVER ( order by id )as RN,gid,Record1,Record2 from tblName

  where Level=0

  )ZZ left outer join

  (

  SELECT

  ROW_NUMBER ( ) OVER ( order by id )as RNN,

  Record1,

  Record2

  from tblName

  where Level=1

  )XX on xx.rnn=zz.rn

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  January 12, 2013 at 8:25 am

  #1576581

  Good to hear

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events