Joining ResultSets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Joining ResultSets

Author

Topic

emmett
Starting Member

4 Posts

Posted - 2006-11-10 : 04:50:57

Hi,

I have two sp's which return the resultsets like these:


SP1

objectID	objectValue1	objectValue2
---------	--------------	---------------
1		     13	       	      27
2		     21		      31
3		   <NULL>	      102
4		      5 	       3	
5		      27	     <NULL>



SP2

objectID	objectValue3	objectValue4
---------	--------------	---------------
1		 11.11.1980	15.03.1988
2		 17.12.1985	  <NULL>
3		 18.05.1997	14.11.1994
4		   <NULL>	21.07.1981	
5		 01.01.2005	  <NULL>

And i want to join these resultsets in a temp table like this:


#tempTableObjects

objectID	objectValue1	objectValue2	objectValue3	objectValue4
---------	--------------	--------------	--------------	---------------
1		     13	       	      27         11.11.1980	15.03.1988
2		     21		      31         17.12.1985	  <NULL>
3		 <NULL>	 	     102         18.05.1997	14.11.1994
4		      5               3		    <NULL>	21.07.1981
5		    27		   <NULL>	  01.01.2005	   <NULL>

I have to use these resultsets in a join query but how?

Or, is there any other solution for this without using cursors?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 04:57:55

[code]
create table #r1
(
objectID int,
objectValue1 int,
objectValue2 int
)

create table #r2
(
objectID int,
objectValue3 int,
objectValue4 int
)

insert into #r1 exec sp1
insert into #r2 exec sp2

select r1.objectID, objectValue1, objectValue2, objectValue3, objectValue4
from #r1 r1 inner join #r2 r2
on r1.objectID = r2.objectID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:58:56

Yes. INSERT the first resultset into a temporary table #temp1.
INSERT the second resultset into a temporary table #temp2.

Now select from the both temporary tables with a JOIN on objectid.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 04:59:07

Firstly you wil have to put both these resultsets into two temp tables:

Insert into #temp1
Exec sp1

Insert into #temp2
Exec sp1

Select t1.ObjectID, t1.ObjectValue1, t1.ObjectValue2,
t2.ObjectValue3, t2.ObjectValue4
From #temp1 t1
join
#temp2 t2
on t1.ObjectID = t2.ObjectID

Sniped!!

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 05:00:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources