Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem in merging 2 sql queries into a single query to distinguish old and new.plz help!! Expand / Collapse
Author
Message
Posted Saturday, January 12, 2013 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
in my table i have the records like

Id Record1 Record2 Level gid
1 testRec1 testRec2 0 1
2 testRec11 testRec22 0 2
3 testRec111 testRec222 0 3
4 testRec111 testRec2222 0 4
5 newtestRec 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
Post #1406339
Posted Saturday, January 12, 2013 8:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 17,843, Visits: 15,790
suvo.kundu (1/12/2013)
in my table i have the records like

Id Record1 Record2 Level gid
1 testRec1 testRec2 0 1
2 testRec11 testRec22 0 2
3 testRec111 testRec222 0 3
4 testRec111 testRec2222 0 4
5 newtestRec 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
Post #1406351
Posted Saturday, January 12, 2013 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
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
Post #1406354
Posted Saturday, January 12, 2013 8:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 17,843, Visits: 15,790
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
Post #1406356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse